Trying to extract from .sas7bdat file

[#1]

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; 

[ChrisHemedinger]

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...)

[#3]

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.

[ChrisHemedinger]

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;

[#5]

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.

[Kurt_Bremser]

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

[#7]

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.

[Kurt_Bremser]

Then you best define a LIBNAME for

R:\Ref\Master Fac\output

and address the dataset through that library.

[ChrisHemedinger]

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";

[Kurt_Bremser]

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.