topic Re: Set up macro to create new datasets while adding a new variable in SAS Programming

Set up macro to create new datasets while adding a new variable

kevsma — Thu, 11 May 2023 17:53:45 GMT

Hello folks, I believe this is an easy one but somehow i can't figure out the macro to do it. I want to create 4 new datasets by specifying the value of a variable in an existing dataset. Below is my code which is very lengthy and redundant.

data rcst1; set lcmf.cm20&fy1two.01a (keep=UCI RCAbrv Status); fy="&fy1two."; 
	where status in ('1','2'); run;

data rcst2; set lcmf.cm20&fy2two.01a (keep=UCI RCAbrv Status); fy="&fy2two."; 
	where status in ('1','2'); run;

data rcst3; set lcmf.cm20&fy3two.01a (keep=UCI RCAbrv Status); fy="&fy3two."; 
	where status in ('1','2'); run;

data rcst4; set lcmf.cm20&fy4two.01a (keep=UCI RCAbrv Status); fy="&fy4two."; 
	where status in ('1','2'); run;

*combine/append all 4 new dataset into one;
data rcstall;
	set rcst1 rcst2 rcst3 rcst4; run;

Two things I wonder whether i can achieve:

1. since the existing macro starts from 1 to 4, can i create another loop to go from 1 to 4 instead of copying and pasting so many lines?

2. can I do one set command to append all four new datasets while adding a new variable "fy" to indicate the source of each dataset?

Appreciate any feedback you may have. Thanks!

Re: Set up macro to create new datasets while adding a new variable

Reeza — Thu, 11 May 2023 18:03:19 GMT

This could possibly be simplified further, what does fy1two to fy4two look like.
The INDSNAME option allows you to identify the input data set names.

Re: Set up macro to create new datasets while adding a new variable

Reeza — Thu, 11 May 2023 18:06:08 GMT

data rcstall;
	set lcmf.cm20&fy1two.01a (keep=UCI RCAbrv Status)
              lcmf.cm20&fy2two.01a (keep=UCI RCAbrv Status)
              lcmf.cm20&fy3two.01a (keep=UCI RCAbrv Status)
              lcmf.cm20&fy4two.01a (keep=UCI RCAbrv Status)
                    INDSNAME = src;

*can use text operations to parse this to desired format;
fy = src;

where status in ('1', '2');

run;

Re: Set up macro to create new datasets while adding a new variable

kevsma — Thu, 11 May 2023 18:49:41 GMT

Thanks @Reeza this works perfectly!