topic Re: Append datasets only if they exist in SAS Programming

Append datasets only if they exist

msf2021 — Thu, 02 Dec 2021 13:34:19 GMT

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!

Re: Append datasets only if they exist

PaigeMiller — Thu, 02 Dec 2021 13:28:54 GMT

What is wrong with this code? Please explain.

Re: Append datasets only if they exist

msf2021 — Thu, 02 Dec 2021 13:34:39 GMT

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

Re: Append datasets only if they exist

PaigeMiller — Thu, 02 Dec 2021 13:37:33 GMT

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

Re: Append datasets only if they exist

msf2021 — Thu, 02 Dec 2021 13:41:28 GMT

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

Re: Append datasets only if they exist

PaigeMiller — Thu, 02 Dec 2021 13:46:06 GMT

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

Re: Append datasets only if they exist

msf2021 — Thu, 02 Dec 2021 13:52:00 GMT

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

Re: Append datasets only if they exist

s_lassen — Thu, 02 Dec 2021 14:09:31 GMT

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;

Re: Append datasets only if they exist

msf2021 — Thu, 02 Dec 2021 14:22:01 GMT

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

Re: Append datasets only if they exist

PaigeMiller — Thu, 02 Dec 2021 14:37:41 GMT

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

Re: Append datasets only if they exist

ballardw — Thu, 02 Dec 2021 17:18:22 GMT

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.

Re: Append datasets only if they exist

Astounding — Thu, 02 Dec 2021 17:26:25 GMT

@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);

Re: Append datasets only if they exist

s_lassen — Fri, 03 Dec 2021 08:33:04 GMT

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:

Moves the contents of STRING to a temporary buffer
Performs CALL CATX(' ',<buffer>,dsname)
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.