topic Re: Appending all datasets in given library in SAS Programming

Appending all datasets in given library

Tal — Tue, 16 Apr 2019 15:03:56 GMT

Hi,

I have a list of several datasets in a library called Y_COMP which I am trying to append to each other but getting errors

The single quotations are also part of the names

'DEC SUMMARY INBOUND SALES$'
'DEC SUMMARY OUTBOUND SALES$'
'DEC SUMMARY SERVICE$'
'FEB SUMMARY INBOUND SALES$'
'FEB SUMMARY OUTBOUND SALES$'
'FEB SUMMARY SERVICE ASSOCIATE$'
'JAN SUMMARY INBOUND SALES$'
'JAN SUMMARY OUTBOUND SALES$'
'JAN SUMMARY SERVICE$'
'MARCH SUMMARY OUTBOUND SALES$'

When running this I am getting the error below:

PROC SQL;
SELECT MEMNAME INTO : MEMNAMES SEPARATED BY ' ' from dictionary.tables
where libname='Y_COMP';quit;

DATA WANT;
SET &MEMNAMES.;
RUN;

ERROR: THESE FILES DONT EXIST:

DEC SUMMARY INBOUND SALES$
DEC SUMMARY OUTBOUND SALES$
DEC SUMMARY SERVICE$
FEB SUMMARY INBOUND SALES$
FEB SUMMARY OUTBOUND SALES$
FEB SUMMARY SERVICE ASSOCIATE$
JAN SUMMARY INBOUND SALES$
JAN SUMMARY OUTBOUND SALES$
JAN SUMMARY SERVICE$
MARCH SUMMARY OUTBOUND SALES$

and this is what I get when running this and the max length of my names is 31 character

DATA WANT;
SET "&MEMNAMES."n;
RUN;

ERROR 307-185: The data set name cannot have more than 32 characters.

Any insights on this please?

Thx

Re: Appending all datasets in given library

ballardw — Tue, 16 Apr 2019 15:19:34 GMT

@Tal wrote:

Hi,

I have a list of several datasets in a library called Y_COMP which I am trying to append to each other but getting errors

The single quotations are also part of the names

'DEC SUMMARY INBOUND SALES$'
'DEC SUMMARY OUTBOUND SALES$'
'DEC SUMMARY SERVICE$'
'FEB SUMMARY INBOUND SALES$'
'FEB SUMMARY OUTBOUND SALES$'
'FEB SUMMARY SERVICE ASSOCIATE$'
'JAN SUMMARY INBOUND SALES$'
'JAN SUMMARY OUTBOUND SALES$'
'JAN SUMMARY SERVICE$'
'MARCH SUMMARY OUTBOUND SALES$'

When running this I am getting the error below:

PROC SQL;
SELECT MEMNAME INTO : MEMNAMES SEPARATED BY ' ' from dictionary.tables
where libname='Y_COMP';quit;

DATA WANT;
SET &MEMNAMES.;
RUN;

ERROR: THESE FILES DONT EXIST:

DEC SUMMARY INBOUND SALES$
DEC SUMMARY OUTBOUND SALES$
DEC SUMMARY SERVICE$
FEB SUMMARY INBOUND SALES$
FEB SUMMARY OUTBOUND SALES$
FEB SUMMARY SERVICE ASSOCIATE$
JAN SUMMARY INBOUND SALES$
JAN SUMMARY OUTBOUND SALES$
JAN SUMMARY SERVICE$
MARCH SUMMARY OUTBOUND SALES$

and this is what I get when running this and the max length of my names is 31 character

DATA WANT;
SET "&MEMNAMES."n;
RUN;

ERROR 307-185: The data set name cannot have more than 32 characters.

Any insights on this please?

Thx

You are not building proper name literal values. Try

PROC SQL;
   SELECT catt(quote(MEMNAME),'n') INTO : MEMNAMES SEPARATED BY ' ' 
   from dictionary.tables
   where libname='Y_COMP';
quit;

and do not use quotes around &memnames.

EACH of your names has to have the quotes and 'n'.

You should have looked at what &memnames contained using a %put &memnames to see if each name was built properly for use in SET statement.

Re: Appending all datasets in given library

Tal — Tue, 16 Apr 2019 16:48:05 GMT

thanks for your response ballardw but I am still getting errors:

ERROR: File WORK.'''DEC SUMMARY INBOUND SALES$'''n.DATA does not exist.

ERROR: File WORK.'''DEC SUMMARY OUTBOUND SALES$'''n.DATA does not exist.

ERROR: File WORK.'''DEC SUMMARY SERVICE$'''n.DATA does not exist.

ERROR: File WORK.'''FEB SUMMARY INBOUND SALES$'''n.DATA does not exist.

ERROR: File WORK.'''FEB SUMMARY OUTBOUND SALES$'''n.DATA does not exist.

ERROR: File WORK.'''FEB SUMMARY SERVICE ASSOCIATE$'''n.DATA does not exist.

The names of my datasets originally have single quotes for example 'DEC SUMMARY INBOUND SALES$' so maybe that causes the errors?

Re: Appending all datasets in given library

ballardw — Tue, 16 Apr 2019 17:58:26 GMT

Since you did not include the LIBRARY name in your original names of the data sets you wanted, I did not either.

PROC SQL;
   SELECT catt('Y_COMP.',quote(MEMNAME),'n') INTO : MEMNAMES SEPARATED BY ' ' 
   from dictionary.tables
   where libname='Y_COMP';
quit;

so you need to have the LIBRARY with EACH data set.

I go out of the way to avoid ever working with name literals for data sets or variables as they cause lots of headaches.

Re: Appending all datasets in given library

Tal — Tue, 16 Apr 2019 20:01:51 GMT

right, forgot the lib name

Thank you ballardw!

So much appreciated!