%MACRO DO_LIST; %DO I = 1 %TO 8;
data sal&i.;
set sal ;
where substr(year,4,1) ="&i" and num=&i;
run;
%END;
%MEND DO_LIST;
It works well with individual files. I think I need to make it more efficiently by replacing these numbers with do loop or sas macro. They are just numbers from 1 to 8. When I ran it, it did not generates those datasets I need.
Any advice? Thank you.
Isn't all that logic equivalent to a single data step below?
You're checking for the num in 1 to 8 and if the first digit equals the variable Num?
data sal_final;
set sal;
first_digit=year;
first_char =char(first_digit, 1);
first_char_num = input(first_char, 8.);
if first_char_num eq num and first_char_num in (1:8);
run;
Working or not, you are trying to generate an inefficient program here. The macro generates code like this:
data sal1;
set sal;
where substr(year, 4, 1) = "1" and num=1;
run;
data sal2;
set sal;
where substr(year, 4, 1) = "2" and num=2;
run;
...
data sal8;
set sal;
where substr(year, 4, 1) = "8" and num=8;
run;
First, consider whether this is the right code to produce the data sets you are seeking. Then consider that this program has to read the data set SAL 8 times. If you were to eliminate the macro language, you could certainly come up with a program that read the data set SAL once instead of 8 times, while creating 8 output data sets. Once you design that program, you will have a prototype that you can use for rewriting the macro to make it more efficient.
you are right, i do need to generate 8 datasets,
i need to select each different row for different year
any other advice instead of using macro?
Absolutely. I assume you are using a macro, then, because you don't know that SAS will let you create multiple data sets in a single step:
data sal1 sal2 sal3 sal4 sal5 sal6 sal7 sal8;
set sal;
length first_digit $ 1;
first_digit = year;
if first_digit="1" and num=1 then output sal1;
else if first_digit="2" and num=2 then output sal2;
...
else if first_digit="8" and num=8 then output sal8;
drop first_digit;
run;
Such a program can be generated with macro language, but it's important to establish that the logic of the program is correct first.
this centainly works, but I have to type each line. and my final output will be
data salfinal;
set sal1 sal2 sal3 sal4 sal5 sal6 sal7 sal8;
run;
any other advice?
Thank you
Definitely. There's still a lot of copying of data sets going on. It looks like you don't care about the data sets sal1 through sal8, and all you really need is salfinal. Is that right? And would this program properly create the data set you want?
If the answer is yes to everything, we can talk about macro language so that you won't have to type so much.
yes, I just need the salfinal dataset. I think if sas macro can be used, there would not be so many lines
thank you
Isn't all that logic equivalent to a single data step below?
You're checking for the num in 1 to 8 and if the first digit equals the variable Num?
data sal_final;
set sal;
first_digit=year;
first_char =char(first_digit, 1);
first_char_num = input(first_char, 8.);
if first_char_num eq num and first_char_num in (1:8);
run;
this worked very well if the numbers are from 1 to 8.
what if the number list includes more numbers, such as two digit numbers?
my actual number list will be 1,2,3, 4, 5, 6, 12,13,22,23; would you please let me know how to modify your code?
Thank you
@Bal23 wrote:
this worked very well if the numbers are from 1 to 8.
what if the number list includes more numbers, such as two digit numbers?
my actual number list will be 1,2,3, 4, 5, 6, 12,13,22,23; would you please let me know how to modify your code?
Thank you
You need to explain better what you have. For example is YEAR varaible character or numeric? If it is character then perhaps you just want to use the : modifier in your comparison?
data want ;
set have;
if year in: ('1','2','3', '4', '5', '6', '12','13','22','23');
run;
But then why would you include '12','13','22' and '23' as separate items in the list since they would already be covered already by '1' and '2'. A value that starts with '12' also starts with '1'.
@Bal23 wrote:
this worked very well if the numbers are from 1 to 8.
what if the number list includes more numbers, such as two digit numbers?
my actual number list will be 1,2,3, 4, 5, 6, 12,13,22,23; would you please let me know how to modify your code?
Thank you
I'm not sure without seeing more data. Provide more details if you want further assistance.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.