BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bal23
Lapis Lazuli | Level 10
%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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

10 REPLIES 10
Astounding
PROC Star

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.

Bal23
Lapis Lazuli | Level 10

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?

Astounding
PROC Star

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.

Bal23
Lapis Lazuli | Level 10

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

Astounding
PROC Star

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.

Bal23
Lapis Lazuli | Level 10

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

thank you

Reeza
Super User

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;
Bal23
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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

 

 

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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