BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chhl
Calcite | Level 5

Hi all,

 

I have some population data from 2007 to 2017 (one table for each year) and I'm trying to create a macro that creates a table for each year with an id, muncipality of residence and muncipality of residense for each of the following four years and merge them together in one table. Here is my code (I'm using SAS 9.4 if that makes any difference): 

 

%macro sqlloop(start, end);

proc sql;

%do year0=&start. %to end;

%let year1=%eval(year0+1);

%let year2=%eval(year0+2);

%let year3=%eval(year0+3);

%let year4=%eval(year0+4);

 

proc sql;

create table mun&year0. as

select t1.id,

       t1.mun as mun&year0.,

       t2.mun as mun&year1.,

       t3.mun as mun&year2.,

       t4.mun as mun&year3.,

       t5.mun as mun&year4.,

       &year0. as year

from pop&year0. as t1 left join pop&year1. as t2 on t1.id = t2.id

left join pop&year2. as t3 on t1.id = t3.id

left join pop&year3. as t4 on t1.id = t4.id

left join pop&year4. as t5 on t1.id = t5.id

order by id;

 

%end;

quit;

%mend;

 

%sqlloop(start=2007, end=2017)

 

data mun07_2017;

       set mun2007 mun2008 mun2009 mun2010 mun2011 mun2012 mun2013 mun2014 mun2015 mun2016 mun2017;

run;

 

The problem that I have is that the macro stops after 2013, because it can't find a table for 2018 (residence 4 years after), which of course doesn't exist. Therefore, I want a condition that sets residence as missing if the corresponding table doesn't exist. I have already tried multiple solutions, but nothing have worked for me so far.

 

Hope you can help!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Suggestions:

If this data is to be used for awhile I would suggest moving it to a library other than work.

 

You can use the EXIST function to test if a set exists wherever it is used such as this example for the last one:

proc sql;
create table mun&year0. as
select t1.id,
       t1.mun as mun&year0.,
       t2.mun as mun&year1.,
       t3.mun as mun&year2.,
       t4.mun as mun&year3.,
%if %sysfunc(exist(pop&year4.))%then %do;
       t5.mun as mun&year4.,
%end;
       &year0. as year
from pop&year0. as t1 left join pop&year1. as t2 on t1.id = t2.id
left join pop&year2. as t3 on t1.id = t3.id
left join pop&year3. as t4 on t1.id = t4.id
%if %sysfunc(exist(pop&year4.))%then %do;
   left join pop&year4. as t5 on t1.id = t5.id
%end;
order by id;
 

Note that the %if can only be used inside a macro such as you using.

 

And shouldn't that Eval be using &start instead of year0???

View solution in original post

2 REPLIES 2
ballardw
Super User

Suggestions:

If this data is to be used for awhile I would suggest moving it to a library other than work.

 

You can use the EXIST function to test if a set exists wherever it is used such as this example for the last one:

proc sql;
create table mun&year0. as
select t1.id,
       t1.mun as mun&year0.,
       t2.mun as mun&year1.,
       t3.mun as mun&year2.,
       t4.mun as mun&year3.,
%if %sysfunc(exist(pop&year4.))%then %do;
       t5.mun as mun&year4.,
%end;
       &year0. as year
from pop&year0. as t1 left join pop&year1. as t2 on t1.id = t2.id
left join pop&year2. as t3 on t1.id = t3.id
left join pop&year3. as t4 on t1.id = t4.id
%if %sysfunc(exist(pop&year4.))%then %do;
   left join pop&year4. as t5 on t1.id = t5.id
%end;
order by id;
 

Note that the %if can only be used inside a macro such as you using.

 

And shouldn't that Eval be using &start instead of year0???

chhl
Calcite | Level 5

Thank you very much!

 

Worked just as I wanted.

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
  • 2 replies
  • 3174 views
  • 0 likes
  • 2 in conversation