topic Re: Add a variable by Macro. Half way done. in SAS Data Science

Add a variable by Macro. Half way done.

肉肉 — Thu, 15 Dec 2016 22:26:21 GMT

Hello Guys,

I have been trying to append tables together by marco way, and it was successful. Furthermore, I wanted to add one more column to each table to display the year and mnth.

For eg:

Data pulled from 201610 (2016 Oct), will have an additonal column to display 201610 accordingly.

Data pulled from 201611 (2016 Nov), will have an additonal column to display 201611 accordingly.

...

All the way to 201301 (2013 Jan).

I have successfully append all the tables from 201301 to 201611, but I just could not figure out how to add an additional column to display the according month.

Below its my code for appending tables with Macro, could any body help me to modify my code to add an additional column?

%macro product; /*This is the Macro code to generate statment with format MCCYYYYMM.MALD_STMT. */

%local year month fname ;

%do year=2013 %to 2016;

%do month=1 %to 12;

%let fname=MC&year.%sysfunc(putn(&month,Z2)).Acct;

%if %sysfunc(exist(&fname)) %then &fname ;

%end;

%mend;

data TotalStatement ; /*this is the step to append all the Acct tables from MC201301 TO MC201612. */

set %product ;

run;

Re: Add a variable by Macro. Half way done.

ballardw — Thu, 15 Dec 2016 22:41:54 GMT

Two things: You may be creating extra work work. It looks like you could use a shortcut list to combine your sets.

data TotalStatement ;

set MC: ; <= Note the : immediately after the C

run;

Will combine all of your data sets that start with MC. Or possibly: Set MC20: ; to get the sets that start with MC20. If they are all in a library other than work just add the library: set MyLib.MC20:

Second is to use the option INDSNAME that would tell you the name of the source file:

data TotalStatement ;

set MC: indsname=tempname;

length datasetname $ 41;

datasetname = tempname;

run;

Or you could parse the tempname variable for your date information. The Indsname option creates a temporary variable that is not added to the dataset. By creating a separate variable that is permanent we can assign the value and keep it.

Re: Add a variable by Macro. Half way done.

肉肉 — Thu, 15 Dec 2016 23:19:30 GMT

Hi ballardw,

Thank you for your help.

It kinds worked, but did not help to get exactly I wanted.

The story is...every month has a table which contains about 612k observations, but it did not have "month" indicator, and I need to add one, so I know which month the data was pulled from. The library name is MC201611, MC201610.....MC201301(there are more, till 200801).

What I wanted was to add a column to indicate the month, so when I pull one observation, I know which month it was from.

Hope this will clear the confusion before.

Re: Add a variable by Macro. Half way done.

ballardw — Fri, 16 Dec 2016 00:21:51 GMT

Are youe dealing with LIBRARY of MC201611 or DATASET MC201611? They are slightly different and would appear in a different location in the dataset name.

If the datasetname is MC201611 the you could do either:

Month= compress(tempname,,'DK');

which would yield 201611 as a character variable

OR do you want an actual SAS date value?

Month= input(compress(tempname,,'DK'),YYMM6.);

format Month YYMMN6.;

Re: Add a variable by Macro. Half way done.

Shmuel — Fri, 16 Dec 2016 00:55:11 GMT

a line taken from your macro:

%if %sysfunc(exist(&fname)) %then &fname ;

are you sure that 201612 already exists ?

Re: Add a variable by Macro. Half way done.

肉肉 — Fri, 16 Dec 2016 04:27:11 GMT

it would be data set which are named as 'MC201611.ACCT', 'MC201610.ACCT', 'MC2016.ACCT'......all the way to 'MC201301.ACCT'. I will need to append them together, with a column named 'MONTH' which works as indicator to display cooresponding month that the data sets were pulled from.

did this help you to understando what I am trying to achieve?

Thank you.

Re: Add a variable by Macro. Half way done.

肉肉 — Fri, 16 Dec 2016 04:28:15 GMT

I guess, if its not existing, it would not be appended. it worked for me.

Re: Add a variable by Macro. Half way done.

ballardw — Fri, 16 Dec 2016 16:12:48 GMT

How about selecting the ones that exist that the current SAS session knows about:

proc sql noprint;
   select catx('.',libname,memname) into : sets separated by ' '
   from dictionary.members 
   where substr(LIBNAME,1,3)='MC2' and memname="ACCT";
quit;


data TotalStatement ;
   set &sets indsname=tempname;
    length datasetname $ 41;
    Month= compress(tempname,,'DK');
    Monthdate= input(compress(tempname,,'DK'),YYMM6.);
    Format Monthdate YYMMN6.;
run;

The Proc SQL section finds all of the libraries that start with MC2 and have a data set named ACCT and put them into a macro variable which is used in SET statement.

Re: Add a variable by Macro. Half way done.

Shmuel — Fri, 16 Dec 2016 20:59:06 GMT

To check you code I made a sligh change:

%macro product;   /*This is the Macro code to generate statment with format MCCYYYYMM.MALD_STMT. */
%local year month fname ;
%do year=2013 %to 2016;
   %do month=1 %to 12;
       %let fname=MC&year.%sysfunc(putn(&month,Z2)).Acct;
      /*  %if %sysfunc(exist(&fname)) %then &fname ;  <<< line replaced by next one */
       %put &FNAME;       
    %end;
 %end;
 %mend;
 
data TotalStatement ;   /*this is the step to append all the Acct tables from MC201301 TO MC201612. */
  set %product ;
    ;    /* <<< one more semicolon added to close SET statement */
run;

check generated code by macro in the log. All file names created as expected;

if file MC201612.acct exists then the only real change is adding the semicolon after set statement.