topic Re: Using macros to import ssp file in New SAS User

Using macros to import ssp file

Banke — Wed, 17 Nov 2021 22:06:34 GMT

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

Re: Using macros to import ssp file

PaigeMiller — Wed, 17 Nov 2021 22:16:31 GMT

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.

Re: Using macros to import ssp file

Banke — Wed, 17 Nov 2021 22:25:37 GMT

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.

Re: Using macros to import ssp file

PaigeMiller — Wed, 17 Nov 2021 22:28:46 GMT

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

How would macros help here?

Re: Using macros to import ssp file

Banke — Wed, 17 Nov 2021 22:28:48 GMT

Text

Re: Using macros to import ssp file

sbxkoenk — Wed, 17 Nov 2021 22:33:31 GMT

@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

Re: Using macros to import ssp file

PaigeMiller — Wed, 17 Nov 2021 22:36:05 GMT

@Banke wrote:
Text

Repeating: "How would macros help here?"

What would the macro do?

Re: Using macros to import ssp file

Banke — Wed, 17 Nov 2021 22:44:59 GMT

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

Re: Using macros to import ssp file

Banke — Wed, 17 Nov 2021 22:50:19 GMT

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

Re: Using macros to import ssp file

sbxkoenk — Wed, 17 Nov 2021 22:50:29 GMT

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

Re: Using macros to import ssp file

Reeza — Wed, 17 Nov 2021 22:53:54 GMT

@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%20text.&text=SSP%20files%20can%20be%20evaluated,used%20to%20generate%20web%20documents.

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:

Get a list of files you need to process. See the third link below or here if you need help with that.
Make working code for one iteration - you have that now.
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-Appendix/ta-p/291716

Re: Using macros to import ssp file

Banke — Wed, 17 Nov 2021 22:54:11 GMT

Yes, that's what i want to do

Thanks

Re: Using macros to import ssp file

Banke — Wed, 17 Nov 2021 22:58:43 GMT

Thanks a lot, I will try it

Re: Using macros to import ssp file

sbxkoenk — Wed, 17 Nov 2021 23:02:02 GMT

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?homeOnFail

... and let us know if you succeed.

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

Good luck,

Koen

Re: Using macros to import ssp file

Banke — Fri, 19 Nov 2021 07:56:48 GMT

Ok, thanks

Re: Using macros to import ssp file

Kurt_Bremser — Fri, 19 Nov 2021 08:30:19 GMT

@sbxkoenk I can't find any official documentation of the XCOPY procedure on documentation.sas.com. Is there any resource for this procedure?

Re: Using macros to import ssp file

sbxkoenk — Fri, 19 Nov 2021 08:40:32 GMT

Hello @Kurt_Bremser ,

I did not know PROC XCOPY either.

It appears in the documentation in examples where they are dealing with the Medical Expenditure Panel Survey (MEPS) data.

Like here :
SAS/STAT® 15.2 User's Guide
The SURVEYLOGISTIC Procedure
Example 119.2 The Medical Expenditure Panel Survey (MEPS)
https://go.documentation.sas.com/doc/en/statug/15.2/statug_surveylogistic_examples02.htm

But I cannot find "official" PROC XCOPY doc (the way other procedures are documented). Not on the web and not in the Windows help that comes with my SAS 9.4 M7 localhost installation.

Strange indeed!

Thanks,

Koen

Re: Using macros to import ssp file

Banke — Fri, 19 Nov 2021 08:45:41 GMT

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.

Re: Using macros to import ssp file

Banke — Fri, 19 Nov 2021 08:53:32 GMT

You are right. I am using it for a MEPS project. They have a macro code for importing directly from their website but it didnt work for me.

Possibly bacuse its quite complicated and i understand it.

Please find the link below:

https://github.com/HHS-AHRQ/MEPS-workshop/blob/master/sas_exercises/Automate_Load_SAS9_Files.sas

Re: Using macros to import ssp file

Banke — Sat, 20 Nov 2021 07:04:24 GMT

Hello all,

Thanks very much for your help.

Thank you so much

@sbxkoenk @PaigeMiller @Reeza and @Kurt_Bremser

This worked:

%let path = C:\Users\HP SPECTRE\OneDrive\MEPSProject;
libname MEPS_P "&path.";

%macro imp (MEPFILE);
filename &MEPFILE.  "&path.\RAW\&MEPFILE..ssp";
proc xcopy in = &MEPFILE. out = MEPS_P import;
run;
%mend imp;

%imp (H163);
%imp (H171);
%imp (H181);
%imp (H162);
%imp (H170);
%imp (H180);
%imp (H160a);
%imp (H168a);
%imp (H178a);