DATA Step, Macro, Functions and more

how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

Hello,

 

I want to append n datasets data_1 ... data_n, which have no systematic pattern in the dataset name, and create a new data set data_final. Besides appending one by one, is there any other method to append one time?

 

Thanks  


Accepted Solutions
Solution
‎08-25-2017 05:12 AM
Trusted Advisor
Posts: 1,831

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

[ Edited ]

@RW9 - it was said that "which have no systematic pattern in the dataset name".

 

@Nieves - as "All the data are from the same library" and assuming you want all of them then

you can create the list of datasets names by:

data _null_;
set sashelp.vmember (where=(libname=<uppercase libref>)) end=eof;
lengt list_names $200; /* addapt length as need */
retain list_names ' ';
list_names = catx(' ',list_names,memname);
if eof then call symput('list_names', trim(list_names));
run;
data final;
set &list_names;
run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,831

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

What about:

data final;
  set data1 data2 data3 .....datan;
run;

If those datasets are all in one library and the only one (no other datasets in it) or

if you have any criterion to identify and select them from a greater list, then you can select the list 

from sql dictionary.rables or from sashelp.tables, assign the list in sas macro variable (using symput function) and use it as:

data final;
 set &list_names;
run;
Contributor
Posts: 20

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

Hi Shmuel,

 

Thanks for your advice. All the data are from the same library and in the same folder. in this case, is macro still needed?

 

Thanks

Super User
Super User
Posts: 9,427

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

Please give some specific examples (datastesps in a code window - {i} above post) for clarification.  From what you have posted:

data want;
  set data_:;
run;

Will set all datasets with prefix data_ in work together, assuming they are all the same structure.  

Solution
‎08-25-2017 05:12 AM
Trusted Advisor
Posts: 1,831

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

[ Edited ]

@RW9 - it was said that "which have no systematic pattern in the dataset name".

 

@Nieves - as "All the data are from the same library" and assuming you want all of them then

you can create the list of datasets names by:

data _null_;
set sashelp.vmember (where=(libname=<uppercase libref>)) end=eof;
lengt list_names $200; /* addapt length as need */
retain list_names ' ';
list_names = catx(' ',list_names,memname);
if eof then call symput('list_names', trim(list_names));
run;
data final;
set &list_names;
run;
Contributor
Posts: 20

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

Thank you very much for the help @Shmuel @RW9

Trusted Advisor
Posts: 1,831

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

Sorry but I had a bug - the proc sql entered only first member to the list.

 

I have edited my code and replaced the sql with a data step, half tested.

I hope this time it will realy give you the desired solution.

Super User
Super User
Posts: 9,427

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

Yes, I saw that and its why asked for specific examples rather than the generic approach which would result in the answer I gave.  

If they are all the same structure then why are they split up in the first place - sounds like an issue further up the line not using by groups or such like.

If they are not the same structure, which different names would indicate, then they wouldn't set together well (i.e differing attributes), and in which case your answer would be used (although maybe consider proc append rather than datastep).

Contributor
Posts: 20

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

My data has the same structure inside (no of columns and names of variables) but I do not properly name all the datasets in a systematic way. Since each dataset is named after a ticker, I include all datasets in one folder. 

Super User
Super User
Posts: 9,427

Re: how to append n dataset data_1 ... data_n to one dataset and create a new data_final?

Two good tips for the future then which will make your coding simpler and easy to maintain.  Use by groups rather than splitting data up and then looping over it - by group processing is quicker and inbuilt into SAS, for instance I could create datasets from proc means to output to a new dataset for each variable and call the dataset variable name - ending up with N number of datasets all having the same structure and differing names - causes more work down the line, or I could transpose the data, then do the proc means with by _name_, thus having one dataset with all the data in there, no looping.

If you have to have multiple repeating data, then its always a good idea to use prefixN, much the same with variables, using prefixN makes coding easier.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 185 views
  • 8 likes
  • 3 in conversation