Creating a CSV Structure Import file from a SAS Data Set
A useful feature in the data structure module of CPDM3.x is the ability to import and export data structures from CSV files. Often you may have existing SAS data sets from which you want to create the CSV structure definitions.
The following macro is for use in base SAS outside of CPDM. It will read a data structure and output a CSV file which contains the structure meta data. This file can then be imported into the data structure module.
Two macro variables are used to specify the input and output locations
The macro takes four parameters as follows:
Macro Code
The following code block shows the macro code. It is also attached as a file at the bottom of this post.
A useful feature in the data structure module of CPDM3.x is the ability to import and export data structures from CSV files. Often you may have existing SAS data sets from which you want to create the CSV structure definitions.
The following macro is for use in base SAS outside of CPDM. It will read a data structure and output a CSV file which contains the structure meta data. This file can then be imported into the data structure module.
Two macro variables are used to specify the input and output locations
- %inpath - specifies path to data location
- %outpath - specifies path to the output location
The macro takes four parameters as follows:
- inds - Input data set name (Required)
- file - Output csv file name (Not Required - if blank, inds is used.)
- drop - Variables to drop from input data set
- keep - Variables to keep from input data set
Macro Code
The following code block shows the macro code. It is also attached as a file at the bottom of this post.
Code:
/***************************************************************** Program: dstocsv.sas Programmer: G. Wagner Date: 07/26/04 Creates a CSV file from a SAS data set which can then be imported to ClinPlus Data Management Macro Variables Specify the inpath and outpath in the %let statement to specify the location of the input data sets and the location to write the csv files. DSTOCSV Macro parameters inds - Input data set name (Required) file - output csv file name (Not Required - if blank, inds is used.) drop - Variables to drop from input data set keep - variables to keep from input data set ************************************************** ***************/ ***** User Change Area *************************************; ** Specify the location of the input path **; %let inpath=c:\documents and settings\gwagner\desktop; ** Sepcify the output location **; %let outpath=c:\documents and settings\gwagner\desktop; ******************* *************************************; libname inpath "&inpath"; %macro dstocsv(inds,file,drop,keep); %if &file >'' %then %do; filename fout "&outpath\&file..csv"; %end; %else %do; filename fout "&outpath\&inds..csv"; %end; %if &keep > ' ' %then %do; data work._dtemp_; set inpath.&inds(keep=&keep); run; %end; %else %if &drop > ' ' %then %do; data work._dtemp_; set inpath.&inds(drop=&drop); run; %end; %else %do; data work._dtemp_; set inpath.&inds(drop=&drop); run; %end; proc contents data=work._dtemp_ out=_ftemp_ noprint; run; proc format; value type 1='N' 2='C'; run; ** Create Format and Informat **; data _ftemp_; length fmt infmt $30; set _ftemp_; ** Clean up character format scrap **; if format='$' then format=''; ** Clean up character informat scrap **; if informat='$' then informat=''; if format > '' then do; fmt=left(trim(format)); if formatl > 0 then fmt=left(trim(fmt))||left(trim(put(formatl,best8.))); fmt=left(trim(fmt))||'.'; if formatd > 0 then fmt=left(trim(fmt))||left(trim(put(formatd,best8.))); end; if informat > '' then do; infmt=left(trim(informat)); if informl > 0 then infmt=left(trim(infmt))||left(trim(put(informl,best8.))); infmt=left(trim(infmt))||'.'; if informd > 0 then infmt=left(trim(infmt))||left(trim(put(informd,best8.))); end; run; ** Write out CSV file with data null **; data _null_; file fout; set _ftemp_; length clength 5; format type type.; *clength=compress(put(length,best5.)); string= trim(NAME)||','|| left(trim(COMPRESS(LABEL,"'")))||','|| left(trim(put(TYPE,type.)))||","|| compress(length)||","|| left(trim(FORMAT))||","|| left(trim(INFORMAT)); PUT STRING; RUN; %MEND dstocsv; ** Sample Macro Call **; %dstocsv(Patient_Sample,,,);