Tag: proc append

SAS: Data Set Compilation through Remote Submit and Download

Sometime it is better to submit the block of codes to the server for execution when the data sets are large and multiple years of data needed to be  compiled.  The remote calculation can also free up the calculation capacity on your own PC, so you can work on something else while waiting for the server.

The following codes are used to:

  • loop through 5 reporting periods each year;
  • retrieve specific variables that meet the conditions;
  • compile data set for each year;
  • stack data sets for multiple years and generate final data set with all years records for download;
  • delete data set for each year in the workspace on the server

The caveats for remote submit are:

  • codes need to be thoroughly validated locally before submit to the server
  • use “nodetails” and “nolist” to omit output report

To remote submit the code, you first select the code block,  then right click and then select “Remote Submit…”.

%let varlist1 = var1 var2 var3 var4 var5 var6 var7 ;
proc datasets library = work nodetails nolist;
delete opsis;
run;
%macro opsis (strtyear, stopyear);
%do yr= &strtyear %to &stopyear;
%put &yr.;
%let nextyr = %eval(&yr.+1);
%let charyr = %substr(&yr.,3,2);
%let charnextyr = %substr(&nextyr.,3,2);
%put &charyr.;
DATA opsis_&charyr.;
SET opsis.uecJun&charyr. (keep=&varlist1.)
opsis.uecJul&charyr. (keep=&varlist1.)
opsis.uecNov&charyr. (keep=&varlist1.)
opsis.uecFeb&charnextyr. (keep=&varlist1.)
opsis.uecMar&charnextyr. (keep=&varlist1.);
where var1 ne 'XX' and var2 ne '' and var3= 'XXX';
proc append base=opsis data =opsis_&charyr.;
run;
%end;
%mend opsis;
%opsis (2009, 2017);
proc download data = opsis out=opsis;run;
proc datasets library=work nodetails;
delete opsis_09 opsis_10-opsis_17;
run;

Macro: Check variable length, name, type of multiple tables with similar layout

/* set up list for the tables requied to scan for  the variables */

%let tlst = a b c d e f g;

/* Method 1: use parameter */

/* Generate vairable list for each table */

%macro varlen (sec=);
%let i = 1;
%do %while (%scan(&sec., &i, ‘ ‘) ne );
%let tbl= %scan(&sec., &i, ‘ ‘);
proc contents data = section&tbl._ug out=ug&tbl. (keep= MEMNAME name type length) noprint;
proc contents data = section&tbl._gr out=gr&tbl. (keep= MEMNAME name type length) noprint;
%let i = %eval (&i +1);
%end;
run;
%mend varlen;
%varlen (sec = &tlst.);

/* Method 2: use macro variable list directly */

/* Append varaible lists to one table */
%macro comp ;
%let i = 1;
%do %while (%scan(&tlst., &i, ‘ ‘) ne );
%let tbl= %scan(&tlst., &i, ‘ ‘);
proc append base = uglen  data = ug&tbl.;
run;
%let i = %eval (&i +1);
%end;
%let i = 1;
%do %while (%scan(&tlst., &i, ‘ ‘) ne );
%let tbl= %scan(&tlst., &i, ‘ ‘);
proc append base = grlen  data = gr&tbl.;
run;
%let i = %eval (&i +1);
%end;
proc sort data = uglen;
by name memname;
proc sort data = grlen;
by name memname;
run;
%mend comp;
%comp ;