DATA Step, Macro, Functions and more

how to replace those numbers with macro or do loop

Accepted Solution Solved
Reply
Super Contributor
Posts: 345
Accepted Solution

how to replace those numbers with macro or do loop

%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.


Accepted Solutions
Solution
‎07-14-2017 03:07 PM
Super User
Posts: 19,867

Re: how to replace those numbers with macro or do loop

Posted in reply to Astounding

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;

View solution in original post


All Replies
Super User
Posts: 5,516

Re: how to replace those numbers with macro or do loop

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.

Super Contributor
Posts: 345

Re: how to replace those numbers with macro or do loop

Posted in reply to Astounding

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?

Super User
Posts: 5,516

Re: how to replace those numbers with macro or do loop

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.

Super Contributor
Posts: 345

Re: how to replace those numbers with macro or do loop

Posted in reply to Astounding

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

Super User
Posts: 5,516

Re: how to replace those numbers with macro or do loop

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.

Super Contributor
Posts: 345

Re: how to replace those numbers with macro or do loop

Posted in reply to Astounding

yes, I just need the salfinal dataset. I think if sas macro can be used, there would not be so many lines

thank you

Solution
‎07-14-2017 03:07 PM
Super User
Posts: 19,867

Re: how to replace those numbers with macro or do loop

Posted in reply to Astounding

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;
Super Contributor
Posts: 345

Re: how to replace those numbers with macro or do loop

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

Super User
Super User
Posts: 7,076

Re: how to replace those numbers with macro or do loop

[ Edited ]

wenling 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'.

 

 

Super User
Posts: 19,867

Re: how to replace those numbers with macro or do loop


wenling 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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 290 views
  • 5 likes
  • 4 in conversation