topic Re: Trying to extract from .sas7bdat file in SAS Programming

Trying to extract from .sas7bdat file

anonymous_user — Wed, 27 Apr 2022 15:02:00 GMT

How do I extract data from a .sas7bdat file?

libname fac "&cipath./Ref/MasterFac/output";

proc sql;
create table irf
as select *
from foundry.inpatientEvents
where serviceFacility = 'PL'
and dischargeDisposition = 'Rehab'
and planPayer='xxx'
and admitdate between "2021-01-01" and "2021-12-31"
and claimrisk=1; 
quit;

DATA fac; 
  set 'R:\Ref\MasterFac\output\fac_npi_network_may_2022.sas7bdat'; 
RUN;

Re: Trying to extract from .sas7bdat file

ChrisHemedinger — Wed, 27 Apr 2022 15:15:56 GMT

Would look something like this, assuming the data set is in the folder you reference in the libname statement (as fac_npi_network_may_2022.sas7bdat) and admitdate is a true date value. Result would land in WORK.IRF.

libname fac "&cipath./Ref/MasterFac/output"; proc sql; create table irf as select * from fac.fac_npi_network_may_2022 where serviceFacility = 'PL' and dischargeDisposition = 'Rehab' and planPayer='xxx' and year(admitdate) = 2021 and claimrisk=1 ; quit;

In your code you reference foundry.inpatientEvents -- is that the true source and the FOUNDRY libname is already defined? If so then you were close:

libname fac "&cipath./Ref/MasterFac/output"; proc sql; create table irf as select * from foundry.inpatientEvents where serviceFacility = 'PL' and dischargeDisposition = 'Rehab' and planPayer='xxx' and year(admitdate) = 2021 and claimrisk=1 ; quit;

(Edited a few times here because I kept rereading and realizing I don't have all of the info for definitive answer...)

Re: Trying to extract from .sas7bdat file

anonymous_user — Wed, 27 Apr 2022 15:15:45 GMT

I've done it that way, and it says the file does not exist. I'm getting this error after the statement libname fac "&cipath./Ref/MasterFac/output"; :
WARNING: Apparent symbolic reference CIPATH not resolved.
NOTE: Library FAC does not exist.

Re: Trying to extract from .sas7bdat file

ChrisHemedinger — Wed, 27 Apr 2022 15:18:47 GMT

CIPATH must be a macro variable that you copied from someone else's code? You could place this at the top, copying the path you reference later.

%let CIPATH = R:\Ref\MasterFac\output;

Re: Trying to extract from .sas7bdat file

Kurt_Bremser — Wed, 27 Apr 2022 15:23:55 GMT

I am a little confused.

Here

libname fac "&cipath./Ref/MasterFac/output";

you use forward slashes, which is typical (and mandatory) for UNIX path names.

But here

set 'R:\Ref\MasterFac\output\fac_npi_network_may_2022.sas7bdat';

you use an obvious Windows path.

If your SAS session does in fact run on a UNIX server, then you won't be able to access a location on your desktop computer like this, even if it is a shared network resource.

Re: Trying to extract from .sas7bdat file

anonymous_user — Wed, 27 Apr 2022 15:31:02 GMT

I'm still getting an error; it looks like I'm missing punctuation or something:

49 proc sql;
50 create table irf2
51 as select *
52 from &cipath.fac_npi_network_may_2022.sas7bdat;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "CIPATH".
52 R:\Ref\Master Fac\output\fac_npi_network_may_2022.sas7bdat
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', '.', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP,
HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

Re: Trying to extract from .sas7bdat file

Kurt_Bremser — Wed, 27 Apr 2022 15:36:11 GMT

Just like in the SET statement of the DATA step, the physical filename in PROC SQL must be enclosed in quotes.

Re: Trying to extract from .sas7bdat file

ChrisHemedinger — Wed, 27 Apr 2022 15:38:11 GMT

Where is your source data exactly? Path and file? And to @Kurt_Bremser 's point, is your SAS really on Windows or is it UNIX?

If it's in

R:\Ref\MasterFac\output\fac_npi_network_may_2022.sas7bdat

Then this would work:

libname fac "R:\Ref\MasterFac\output"; proc sql; create table irf as select * from fac.fac_npi_network_may_2022 where serviceFacility = 'PL' and dischargeDisposition = 'Rehab' and planPayer='xxx' and year(admitdate) = 2021 and claimrisk=1 ; quit;

To clear up a common question about forward/back slashes in SAS: forward slash always works (UNIX or Windows), but backslash works only on Windows.

Hence if you're running on Windows, this is valid:

%let CIPATH = R: ; libname fac "&cipath./Ref/MasterFac/output";

Re: Trying to extract from .sas7bdat file

anonymous_user — Wed, 27 Apr 2022 18:53:54 GMT

I added the quotes, Now it's saying it's too long:

ERROR: The physical file name "R:\Ref\Master Fac\output\fac_npi_network_may_2022.sas7bdat" is too long.
I've tried removing the sas7bdat extension; i've tried using a macro to call the month and year - still get the same error message.

Re: Trying to extract from .sas7bdat file

Kurt_Bremser — Wed, 27 Apr 2022 19:02:30 GMT

Then you best define a LIBNAME for

R:\Ref\Master Fac\output

and address the dataset through that library.