Using macros to import ssp file

[Banke]

Hello everyone,

please I am trying to import a dataset of ssp file using a simple macro but my log says i cant use DBMS for an ssp file. How can I go about this please?

I saw a more complicated code online that  can be used to extract the file directly from the zipped folder in the direct source link but that didnt work too

I have posted the two codes and the log error.

Thanks a lot

/**first code**/

%macro import(meps);

FILENAME HI63 'C:\Users\HP SPECTRE\Documents\meps directory\consolidated\H163.SSP';

PROC IMPORT DATAFILE=HI63 DBMS=SSP OUT=&MEPS. REPLACE; 
GETNAMES=YES;
RUN;
%mend import;

%import (H171);
%import (H181);

run;

/**the second code i got online for getting the dataset  directly from the website**/
%let meps_file = h206b;
%let meps_url = https://meps.ahrq.gov/mepsweb/data_files/pufs/h206b/h206bv9.zip;
%let meps_dir = C:/MEPS/sas_data;


/* DO NOT EDIT this section *******************************/
/* Download zip file from MEPS website to specified directory (meps_dir) */
filename zipfile "&meps_dir/&meps_file.v9.zip";

proc http 
	url = "&meps_url"
	out = zipfile;
run;

/* Unzip SAS dataset and save in specified directory */
filename inzip ZIP "&meps_dir/&meps_file.v9.zip";	
filename sasfile "&meps_dir/&meps_file..sas7bdat" ;
 
data _null_;
	infile inzip(&meps_file..sas7bdat) 
	  lrecl=256 recfm=F length=length eof=eof unbuf;
   file sasfile lrecl=256 recfm=N;
   input;
   put _infile_ $varying256. length;
   return;
 eof:
   stop;
run;
/* End of DO NOT EDIT section ***************************/

/* Read in the saved SAS V9 dataset */
data dn2018;
	set "&meps_dir/&meps_file..sas7bdat";
run;

/* View first 5 rows of dataset */
proc print data = dn2018 (obs = 5);
run;

Log  error for the first code

 

ERROR: DBMS type SSP not valid for import.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

ERROR: DBMS type SSP not valid for import.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

49
50   run;

error for the secon code

ERROR: Entry h155.sas7bdat in zip file C:\Users\HP SPECTRE\Documents\meps directory\h155v9.zip does not exist.
ERROR: Physical file does not exist, h155.sas7bdat.
NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The file SASFILE is:
      Filename=C:\Users\HP SPECTRE\Documents\meps directory\h155.sas7bdat,
      RECFM=N,LRECL=256,File Size (bytes)=0,
      Last Modified=17Nov2021:15:39:25,
      Create Time=17Nov2021:15:39:25

[PaigeMiller]

What is an SSP file?

From now on, please do not separate the error message from the code. Show us the WHOLE complete entire log.

[Banke]

It is a type of file extension. This code worked but im trying learn to use macros

LIBNAME MEPS 'C:\Users\HP SPECTRE\Documents\meps directory';
FILENAME H163 'C:\Users\HP SPECTRE\Documents\meps directory\consolidated\H163.SSP';
PROC XCOPY IN = H163 OUT = MEPS IMPORT;
RUN;

these are the complete logs for the macros i used

/**for first code**/

 %macro import(meps);
39
40   FILENAME HI63 'C:\Users\HP SPECTRE\Documents\meps directory\consolidated\H163.SSP';
41
42   PROC IMPORT DATAFILE=HI63 DBMS=SSP OUT=&MEPS. REPLACE;
43   GETNAMES=YES;
44   RUN;
45   %mend import;
46
47   %import (H171);
ERROR: DBMS type SSP not valid for import.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

48   %import (H181);


ERROR: DBMS type SSP not valid for import.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

49
50   run;

/** second log for the second code**/

%let meps_file = h163;
3    %let meps_url = https://meps.ahrq.gov/mepsweb/data_files/pufs/h163ssp.zip;
4    %let meps_dir = C:\Users\HP SPECTRE\Documents\meps directory;
5
6
7    /* DO NOT EDIT this section *******************************/
8    /* Download zip file from MEPS website to specified directory (meps_dir) */
9    filename zipfile "&meps_dir/&meps_file.v9.zip";
10
11   proc http
12       url = "&meps_url"
13       out = zipfile;
14   run;

NOTE: 200 OK
NOTE: PROCEDURE HTTP used (Total process time):
      real time           1.68 seconds
      cpu time            0.23 seconds


15
16   /* Unzip SAS dataset and save in specified directory */
17   filename inzip ZIP "&meps_dir/&meps_file.v9.zip";
18   filename sasfile "&meps_dir/&meps_file..sas7bdat" ;
19
20   data _null_;
21       infile inzip(&meps_file..sas7bdat)
22         lrecl=256 recfm=F length=length eof=eof unbuf;
23      file sasfile lrecl=256 recfm=N;
24      input;
25      put _infile_ $varying256. length;
26      return;
27    eof:
28      stop;
29   run;

ERROR: Entry h155.sas7bdat in zip file C:\Users\HP SPECTRE\Documents\meps directory\h155v9.zip does not exist.
ERROR: Physical file does not exist, h155.sas7bdat.
NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The file SASFILE is:
      Filename=C:\Users\HP SPECTRE\Documents\meps directory\h155.sas7bdat,
      RECFM=N,LRECL=256,File Size (bytes)=0,
      Last Modified=17Nov2021:15:39:25,
      Create Time=17Nov2021:15:39:25

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds


30   LIBNAME MEPS 'C:\Users\HP SPECTRE\Documents\meps directory';
NOTE: Libref MEPS was successfully assigned as follows:
      Engine:        V9
      Physical Name: C:\Users\HP SPECTRE\Documents\meps directory
31   FILENAME H163 'C:\Users\HP SPECTRE\Documents\meps directory\consolidated';
32   PROC XCOPY IN = H163 OUT = MEPS IMPORT;
NOTE: Writing HTML Body file: sashtml.htm
33   RUN;

NOTE: Input library H163 is sequential.
ERROR: Invalid file, C:\Users\HP SPECTRE\Documents\meps directory\consolidated.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE XCOPY used (Total process time):
      real time           0.53 seconds
      cpu time            0.32 seconds

NOTE: The SAS System stopped processing this step because of errors.

[PaigeMiller]

Yes, I know its a file extension. What is in the file? Text? Hexadecimal characters? Something else?

How would macros help here?

[Banke]

Text

[sbxkoenk]

@PaigeMiller .

When looking on the web I think SSP is a file extension commonly associated with SAS Transport Data Format files. (Although I had never come across it before).

SAS transport (.ssp) files can be read into SAS using PROC XCOPY .

Cheers,

Koen

[Banke]

Thanks a lot Koen. I used proc xcopy and it actually worked. I dont know how use macro with it however. I am going to import a lot of the datasets, so i am asked to use macros to achieve that

[sbxkoenk]

Hello,

You can put all .ssp files of a particular directory on your file system in a SAS dataset (with or without including the full directory path). You can do that using code of course (no hard-coding of the file names needed)!

Then you need to loop over the records in that SAS dataset using a macro.

In every loop you pick up another file and import it with XCOPY procedure.

Is this what you want to do with the macro?

Thanks,

Koen

[Banke]

Yes, that's what i want to do

Thanks

[sbxkoenk]

Hello,

Try to start from here :

SAS 9.4 / Viya 3.5
Macro Language Reference guide
Example 1: Import All CSV Files That Exist within a Directory
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/n0ctmldxf23ixtn1kqsoh5bsgmg8.htm?...

... and let us know if you succeed.

PROC IMPORT should be replaced by PROC XCOPY of course (and .csv by .ssp).

Good luck,

Koen

[Banke]

Ok, thanks

[Banke]

Thanks a lot Koen. I used proc xcopy and it actually worked but I dont know how to use macros with it. I want to import many datasets of the ssp file and merge

[Reeza]

@Banke SSP files can also be HTML/web files so it's important to detail a bit what you're trying to do here especially when dealing with acronyms. 

https://fileinfo.com/extension/ssp#:~:text=An%20SSP%20file%20is%20a,are%20evaluated%20to%20page%20te....

Typically when converting in batch, you either create a list of input files and process them in a loop or if there's a naming convention you can create your loop manually without the list of files. 

So first:

1. Get a list of files you need to process. See the third link below or here if you need help with that.
2. Make working code for one iteration - you have that now.
3. Figure out how to execute that for all files - see second link for that one. 

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[Banke]

Thanks a lot, I will try it

[Banke]

Update:

Thanks all for your inputs.

So i have tried using some macros, here is one I could understand because its simple

my log says that my SAS engine does not support it.

the code without macors however works

/**no macros**/

filename H163 'C:\Users\HP SPECTRE\Documents\meps directory\consolidated\H163.SSP';
proc xcopy in = H163 out = MEPS import;
run;

/**macros**/
%let path = C:\Users\HP SPECTRE\OneDrive\MEPSP;
%let out = &path.\results;

libname MEPSP "&path.";

%macro imp (MYFILE);
filename &MYFILE. "&path.\&MYFILE..SSP";
proc xcopy in  = &MYFILE. out = MEPSP import;
run;
%mend imp;

%imp (H163);

/***log for macros**/

  %LET PATH = C:\Users\HP SPECTRE\OneDrive\MEPSP;
152  %LET OUT = &PATH.\RESULTS;
153
154  LIBNAME MEPSP "&PATH.";
NOTE: Libref MEPSP was successfully assigned as follows:
      Engine:        V604
      Physical Name: C:\Users\HP SPECTRE\OneDrive\MEPSP
155
156  %MACRO IMP (MYFILE);
157  FILENAME &MYFILE. "&PATH.\&MYFILE..SSP";
158  PROC XCOPY IN = &MYFILE. OUT = MEPSP IMPORT;
159  RUN;
160  %MEND IMP;
161
162  %IMP (H163);

NOTE: Input library H163 is sequential.
NOTE: Copying H163.H163 to MEPSP.H163 (memtype=DATA).
NOTE: Libname and/or system options for compress, pointobs, data representation and encoding attributes were used at user's
      request.
ERROR: The V604 engine does not support file creation.
ERROR: File MEPSP.H163.DATA has not been saved because copy could not be completed.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE XCOPY used (Total process time):
      real time           0.17 seconds
      cpu time            0.14 seconds

NOTE: The SAS System stopped processing this step because of errors.