topic Re: How to create mutiple data set from multiple libref? in SAS Programming

How to create mutiple data set from multiple libref?

marathoner — Fri, 15 Oct 2021 14:54:59 GMT

I am new at SAS. I would like to know if the following action is doable. I have two libref. Could I set two libref in a single dataset.

So instead of doing this.

Libname xl1 xlsx "C:\Users\Desktop";
Libname xl2 xlsx "C:\Users\Desktop";

data report1;
	set xl1.sheet1;
run;

data report2;
	set xl2.sheet1;
run;

Could I do something like this?

data report1 report2;
set xl1.sheet1;
set xl2.sheet1;
run;

Re: How to create mutiple data set from multiple libref?

PaigeMiller — Fri, 15 Oct 2021 15:43:26 GMT

Yes, you CAN do this but it seems relatively pointless. First, you need only one LIBNAME statement, instead of referring to the same library (Excel file) twice.

Next, if you want to perform a SET, you should do something like this:

set dataset1 dataset2;

rather than two SET statements.

But essentially, the way you have programmed it, REPORT1 and REPORT2 are identical data sets, and as I said, this is kind of pointless as far as I can see. What are you trying to do?

Re: How to create mutiple data set from multiple libref?

Reeza — Fri, 15 Oct 2021 15:05:14 GMT

No but there are other ways. What are you actually trying to do overall?

You can use PROC DATASETS/COPY to copy data sets from libraries to work or a different location as needed.

Re: How to create mutiple data set from multiple libref?

Tom — Fri, 15 Oct 2021 15:14:46 GMT

You use as many input dataset into your data step as you need.

It you want to concatenate the data (they have the same variables but represent different observations) then just list them on the SET statement.

data want;
  set lib1.ds1 lib2.ds2 ;
run;

If you want to merge them (that is add new variables to the same observations based on some key variables that indicate which observations to combine) then use MERGE and BY statements.

data want;
  merge lib1.ds1 lib2.ds2 ;
  by id;
run;

You can also write multiple datasets in one data step. Just list the names on the DATA statement and then use explicit OUTPUT statements to direct the observations to the appropriate dataset(s). For example you migth want to generate three datasets from a merge, one with the observations that appear in both source datasets and then onse with the observations that appear in only one of the two.

data only1 only2 both;
  merge lib1.ds1(in=in1) lib2.ds2(in=in2);
  by id;
  if in1 and in2 then output both;
  else if in1 then output only1;
  else output only2;
run;

PS: Note that your LIBNAME statement syntax is incorrect. If you are using the XLSX engine the path has to include a filename, not a directory name.

Libname xl1 xlsx "C:\Users\Desktop\xl1.xlsx";

Re: How to create mutiple data set from multiple libref?

PaigeMiller — Fri, 15 Oct 2021 15:50:09 GMT

@marathoner wrote:

So instead of doing this.
Libname xl1 xlsx "C:\Users\Desktop";
Libname xl2 xlsx "C:\Users\Desktop";

Adding, in my opinion, this is a particularly poor place to store data.

Re: How to create mutiple data set from multiple libref?

Kurt_Bremser — Fri, 15 Oct 2021 16:26:01 GMT

This:

Libname xl1 xlsx "C:\Users\Desktop";

can't work. XLSX LIBNAMEs need to point to files, not directories.

Re: How to create mutiple data set from multiple libref?

ballardw — Fri, 15 Oct 2021 17:58:59 GMT

@marathoner wrote:

I am new at SAS. I would like to know if the following action is doable. I have two libref. Could I set two libref in a single dataset.

Could I do something like this?
data report1 report2;
set xl1.sheet1;
set xl2.sheet1;
run;

Try it and see. I might suggest starting with two small data sets with different numbers of records to see the behavior more easily.

With data starting in EXCEL your attempt might fail because different identically named columns have different data types, which is why recommend using SAS data sets to experiment with.