topic Re: Reading in with LIBNAME XLSX Q in SAS Programming

Reading in with LIBNAME XLSX Q

edasdfasdfasdfa — Sun, 07 Feb 2021 15:12:04 GMT

options validvarname=any;

LIBNAME xl XLSX '/home/john/B.xlsx';

Hello,

I'm using the above to read in an Excel file with 2 sheets.

I want these two sheets of info to be combined based on an ID (both have ID columns).

But pretty sure the ID's and maybe even Dates would have to first be sorted? So, first, how can that be done with multiple sheets using libname xlsx?

The reason I ask is because unlike something like proc import where I would have a dataset to sort, here I don't.

Re: Reading in with LIBNAME XLSX Q

PaigeMiller — Sun, 07 Feb 2021 15:20:48 GMT

@edasdfasdfasdfa wrote:

options validvarname=any;

LIBNAME xl XLSX '/home/john/B.xlsx';

I'm using the above to read in an Excel file with 2 sheets.

I want these two sheets of info to be combined based on an ID (both have ID columns).

What do you mean by "combined"?

But pretty sure the ID's and maybe even Dates would have to first be sorted? So, first, how can that be done with multiple sheets using libname xlsx?

The reason I ask is because unlike something like proc import where I would have a dataset to sort, here I don't.

LIBNAME does give you data sets to work with. You sort them using PROC SORT and OUT= to create a new sorted data set. Example:

proc sort data=xl.data1 out=data1;
    by id;
run;

Re: Reading in with LIBNAME XLSX Q

edasdfasdfasdfa — Sun, 07 Feb 2021 15:28:15 GMT

Hi Paige,

Thanks for the quick response.

When you say 'LIBNAME does give you data sets to work with.' ..I do see the two individual Sheets as members.

So would I sort each sheet individually but use the same out= dataset name? does that work?

By combine..I meant that all the observations for a certain ID would be in one row..as opposed to multiple.

Re: Reading in with LIBNAME XLSX Q

PaigeMiller — Sun, 07 Feb 2021 15:39:08 GMT

proc sort data=xl.data1 out=data1;
by id;
run;
proc sort data=xl.data2 out=data2;
by id;
run;
data want;
    merge data1 data2;
    by id;
run;

Re: Reading in with LIBNAME XLSX Q

Tom — Sun, 07 Feb 2021 15:49:08 GMT

Once you have defined a libref with the XLSX engine you can reference each sheet as if it was a dataset. So if you had sheets named SHEET1 and SHEET2 your could MERGE them by ID with code like this:

LIBNAME xl XLSX '/home/john/B.xlsx';
data want;
  merge xl.sheet1 xl.sheet2;
  by id;
run;

If the data in the sheets are not sorted by ID then you will need to sort them first and then merge the sorted versions.

proc sort data=xl.sheet1 out=sheet1_sorted;
  by id;
run;
proc sort data=xl.sheet2 out=sheet2_sorted;
  by id;
run;
data want ;
  merge sheet1_sorted sheet2_sorted;
  by id;
run;

Re: Reading in with LIBNAME XLSX Q

edasdfasdfasdfa — Sun, 07 Feb 2021 16:14:00 GMT

Thanks to both of you. This makes sense.

Two final questions.

What would be the point of sorting BY multiple variables? (multiple variables in BY statement)

And second, what happens if you try to sort on a variable that is already sorted?

Re: Reading in with LIBNAME XLSX Q

PaigeMiller — Sun, 07 Feb 2021 16:16:58 GMT

@edasdfasdfasdfa wrote:

Two final questions.

What would be the point of sorting BY multiple variables? (multiple variables in BY statement)

It sorts by multiple variables.

And second, what happens if you try to sort on a variable that is already sorted?

Nothing.

Re: Reading in with LIBNAME XLSX Q

Tom — Sun, 07 Feb 2021 16:21:12 GMT

You sort by multiple BY variables to establish the order that you want repeating values of the earlier variables to appear. For example you might sort by subject id and visit date.

proc sort data=measurements;
  by id date;
run;

So now the subjects are ordered and the multiple observations for the same subject are ordered chronologically.

Re: Reading in with LIBNAME XLSX Q

RichardDeVen — Sun, 07 Feb 2021 16:34:39 GMT

You can't read more than one sheet at a time, so stacking the sheets with SET xl.sheet1 xl.sheet2;

will cause an ERROR:.

Copy each sheet to WORK, and stack them as desired.

Example:

proc sort data=sashelp.class out=class; by sex name; run; ods excel file='class.xlsx' options(sheet_name='#byval1'); options nobyline; ods noresults; proc print noobs data=class; by sex; run; ods excel close; libname xl excel 'class.xlsx'; * File in user ERROR: ; data want; set indsname = sheetname xl.'F$'n xl.'M$'n ; by name; sex = sheetname; run; data one; set xl.'F$'n; sex='F'; data two; set xl.'M$'n; sex='M'; data want; set one two indsname=sheetname; by name; run; libname xl;

Re: Reading in with LIBNAME XLSX Q

edasdfasdfasdfa — Sun, 07 Feb 2021 16:35:04 GMT

Thanks, Tom.

I like the output the way I have it now...where ID is in order so I can easily see debit/credit over the course of different days..

but is there some kind of merging/joining/combining..that would enable you to only have one row for each ID regardless of how many different transactions on different days there were? or would you handle that just by using some filtering?

Re: Reading in with LIBNAME XLSX Q

edasdfasdfasdfa — Sun, 07 Feb 2021 16:36:56 GMT

I don't get such an error, Richard.