topic how to replace those numbers with macro or do loop in SAS Programming

how to replace those numbers with macro or do loop

Bal23 — Fri, 14 Jul 2017 15:58:53 GMT

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

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

Astounding — Fri, 14 Jul 2017 16:08:12 GMT

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.

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

Bal23 — Fri, 14 Jul 2017 16:12:47 GMT

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?

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

Astounding — Fri, 14 Jul 2017 16:16:37 GMT

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.

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

Bal23 — Fri, 14 Jul 2017 16:24:15 GMT

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

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

Astounding — Fri, 14 Jul 2017 16:32:21 GMT

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.

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

Bal23 — Fri, 14 Jul 2017 16:43:14 GMT

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

thank you

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

Reeza — Fri, 14 Jul 2017 17:09:22 GMT

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;

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

Bal23 — Mon, 24 Jul 2017 16:57:19 GMT

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

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

Tom — Mon, 24 Jul 2017 18:41:21 GMT

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

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

Reeza — Mon, 24 Jul 2017 18:56:50 GMT

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