BookmarkSubscribeRSS Feed
msf2021
Fluorite | Level 6

Hi,

I have many tables with same name conventioning for every month :

TEMPCAAD.LIFT_&NOME_MODELO._&VERSAO_MODELO._'!! put(cur_month,yymmn6.))

I am trying to append all datasets but because i am doing current monht= month now and previousyearmonth= month one year ago and some datasets don't exist, how can i append them only if they exist?

Here is my try:

 

%let currentmonth = &anomes_scores;
%let previousyearmonth = &anomes_x12;


data _null_;
length string $1000;
cur_month = input("&previousyearmonth.01",yymmdd8.);
do until (cur_month > input("&currentmonth.01",yymmdd8.));
  string = catx(' ',trim(string),'TEMPCAAD.LIFT_&NOME_MODELO._&VERSAO_MODELO._'!! put(cur_month,yymmn6.));
  cur_month = intnx('month',cur_month,1,'b');
end;
call symput('mydatasets',trim(string));
run;


%macro checkds(data);
%if %sysfunc(exist(data)) %then %do;
data WORK.LIFTS_U6M;
set data;
run;
%end;
%mend checkds;
%checkds(&mydatasets.)

The problem is that it does not return anything the macro checkds

 

Thanks!

12 REPLIES 12
PaigeMiller
Diamond | Level 26

What is wrong with this code? Please explain.

--
Paige Miller
msf2021
Fluorite | Level 6

The problem is that it does not return anything the macro %checkds

PaigeMiller
Diamond | Level 26

I'm not sure what you mean. What should it return? Please explain in more detail.

--
Paige Miller
msf2021
Fluorite | Level 6

I just need to append 

table1_202001

table2_202002

table3_202003

....

table4_202012

 

but append only if they exist in database, if they don't exist, than just ignore and append the ones that exist

PaigeMiller
Diamond | Level 26

I'm really lost here, your code inside the macro does not append anything, so why are you expecting the macro to do so?

--
Paige Miller
msf2021
Fluorite | Level 6

my previous code before i built the macro was :

 

# create a list of all my datasets 
data _null_;
length string $1000;
cur_month = input("&previousyearmonth.01",yymmdd8.);
do until (cur_month > input("&currentmonth.01",yymmdd8.));
  string = catx(' ',trim(string),'TEMPCAAD.LIFT_&NOME_MODELO._&VERSAO_MODELO._'!! put(cur_month,yymmn6.));
  cur_month = intnx('month',cur_month,1,'b');
end;
call symput('mydatasets',trim(string));
run;

#Append my datasets (all months)
data WORK.LIFTS_U6M;
set &mydatasets.;
run;

but it gives me error when one of my datasets that are inside &mydatastets doesn't exist

s_lassen
Meteorite | Level 14

I think you can just adjust your original code to test in the first data step, e.g.:

# create a list of all my datasets 
data _null_;
length string $1000 dsname $200;
cur_month = input("&previousyearmonth.01",yymmdd8.);
do until (cur_month > input("&currentmonth.01",yymmdd8.));
  dsname='TEMPCAAD.LIFT_&NOME_MODELO._&VERSAO_MODELO._'!! put(cur_month,yymmn6.)
  if exist(dsname) then
    call catx(' ',string,dsname);
  cur_month = intnx('month',cur_month,1,'b');
end;
call symput('mydatasets',trim(string));
run;

#Append my datasets (all months)
data WORK.LIFTS_U6M;
set &mydatasets.;
run;
msf2021
Fluorite | Level 6

not really understanding this part:

 

do until (cur_month > input("&currentmonth.01",yymmdd8.));
  dsname='TEMPCAAD.LIFT_&NOME_MODELO._&VERSAO_MODELO._'!! put(cur_month,yymmn6.)
  if exist(dsname) then
    call catx(' ',string,dsname);
  cur_month = intnx('month',cur_month,1,'b');
end;

the "TEMPCAAD.LIFT_&NOME_MODELO._&VERSAO_MODELO._'!! put(cur_month,yymmn6.)" is the name of the table (TEMPCAAD. is the library and the rest is the name) 

also, when you put 

call catx(' ',string,dsname);

you deleted string

Astounding
PROC Star

@s_lassen 

 

I like this approach (since it is what I would have suggested), but you need a little clean-up here:

dsname='TEMPCAAD.LIFT_&NOME_MODELO._&VERSAO_MODELO._'!! put(cur_month,yymmn6.)

 

Besides missing a semicolon at the end of the statement, you need to switch from single quotes to double quotes, to allow the macro variables to resolve.  Also, for clarity, I might suggest:

 

if exist(dsname) then string = catx(' ', string, dsname);

 

s_lassen
Meteorite | Level 14

You are right about the missing semicolon and the double quotes - although the code (kind of) worked in the original version, because the macro variables would be resolved in the SET statement later. But when calling the EXIST function they do not get resolved.

 

The reason that I normally use CALL CATX instead of a function call (when appending to the same variable) is that it is faster.

 

The assignment 

string = catx(' ', string, dsname);

does something like this:

  1. Moves the contents of STRING to a temporary buffer
  2. Performs CALL CATX(' ',<buffer>,dsname) 
  3. Moves the result back to STRING

The CALL CATX operation just performs step 3. So it both saves CPU time and memory. And once you have learned the syntax of CALL CATX, I do not see any difference in the "clarity" of the statement. It is just that most people use the function all the time, meaning that they are not so used to the CALL syntax. But it is fully documented, and not hard to understand.

PaigeMiller
Diamond | Level 26

@msf2021 wrote:

my previous code before i built the macro was :

 

# create a list of all my datasets 
data _null_;
length string $1000;
cur_month = input("&previousyearmonth.01",yymmdd8.);
do until (cur_month > input("&currentmonth.01",yymmdd8.));
  string = catx(' ',trim(string),'TEMPCAAD.LIFT_&NOME_MODELO._&VERSAO_MODELO._'!! put(cur_month,yymmn6.));
  cur_month = intnx('month',cur_month,1,'b');
end;
call symput('mydatasets',trim(string));
run;

#Append my datasets (all months)
data WORK.LIFTS_U6M;
set &mydatasets.;
run;

but it gives me error when one of my datasets that are inside &mydatastets doesn't exist


You took this part out of the macro shown at the beginning of this thread:

 

set &mydatasets.;

so your original macro doesn't append anything.

 

The way to get this to work is to first write SAS code for two data sets that actually does the appending properly, without macros and without macro variables. This is a must. If you don't have working code without macros and without macro variables, then you will not get a macro to work either.

 

 

--
Paige Miller
ballardw
Super User

A possible alternative may be the use of data set name list.

Dummy example code:

date want;
    set thislib.abc_&pdq_somename: ;
run;

The colon at the end of the name tells SAS that you want to use all of the data sets in the given library, in the example Thislib, whose names start with abc_&pdq_somename.

 

Warning: this is not efficient code for large data sets as it does process each record like any data step code but does not require any step to check for existence of the sets.

If your data sets are not consistent about variable types or lengths you may get warnings about such so you do want to read the log carefully and address those issues.

 

Your initial posted code doesn't work because 1) you did not use the macro parameter data correctly, references require &data and 2) the Exist function would only work with a single data set name and you are attempting to pass a space delimited list.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 12 replies
  • 1882 views
  • 0 likes
  • 5 in conversation