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

I have 400 files to merge as one dataset. They have the same columns, but are of different subjects. So every line in each of the data set needs to be included.

not sure if the "merge by" would work here. Please help

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

Create a macro variable &names that contains all the data set names, replace yourlibnamegoeshere with the actual name of the library, all in UPPER CASE

proc sql noprint;

     select distinct memname into :names separated by ' ' from sashelp.vtable where libname="yourlibnamegoeshere";

quit;

Then concatenate all data sets

data all;

     set &names;

...

--
Paige Miller

View solution in original post

9 REPLIES 9
PGStats
Opal | Level 21

You might not need macro at all.  If your files are text you could read them all in one datastep using wildcards in the infile statement (at least on Windows).

PG

PG
SN
Calcite | Level 5 SN
Calcite | Level 5

they are as SAS datasets in a lib ; Number of rows varies from 1  to 50  rows  in the datasets. Finally, after combining the datasets, I would need to run stats analysis on it.

PaigeMiller
Diamond | Level 26

I said "depending on how the files are named"

You didn't address this. Are they named sequentially, or by some identifier, or otherwise? What are some of the names?

--
Paige Miller
PaigeMiller
Diamond | Level 26

It's not clear that "MERGE" is the way to go. I think SET makes more sense.

Are these 400 files SAS data sets, or external files?

Depending on how the files are named, and what type of files you are working with, we can probably provide more specific advice.

--
Paige Miller
SN
Calcite | Level 5 SN
Calcite | Level 5

Each dataset is a subject data labelled alphanumerically in a random order. There is no pattern in the dataset names.

I have a list of the 400 names in a seperate dataset. I need to write a script which would do this

data=combineddata;

set ds1...ds400;

How do I use macro to call each of the file from the filename dataset and combine them together.

PaigeMiller
Diamond | Level 26

Create a macro variable &names that contains all the data set names, replace yourlibnamegoeshere with the actual name of the library, all in UPPER CASE

proc sql noprint;

     select distinct memname into :names separated by ' ' from sashelp.vtable where libname="yourlibnamegoeshere";

quit;

Then concatenate all data sets

data all;

     set &names;

...

--
Paige Miller
SN
Calcite | Level 5 SN
Calcite | Level 5

Thanks much. this should work.

But I am currently getting an error for most of the variables

ERROR: Variable SBP has been defined as both character and numeric.

ERROR: Variable DBP has been defined as both character and numeric.

Probably the  way forward is to check all the files for the variable type and run this code again.

r

SN
Calcite | Level 5 SN
Calcite | Level 5

Thanks much. The code worked. I found a couple of empty files which gave an error as above.

data_null__
Jade | Level 19

With 400 datasets on the SET statement you may want to look at the SET statement documentation concerning the OPEN=DEFER option.

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
  • 9 replies
  • 1184 views
  • 3 likes
  • 4 in conversation