Creating a CSV Structure Import file from a SAS Data Set

[Greg Wagner]

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

1. %inpath - specifies path to data location
2. %outpath - specifies path to the output location

The macro takes four parameters as follows:

1. inds - Input data set name (Required)
2. file - Output csv file name (Not Required - if blank, inds is used.)
3. drop - Variables to drop from input data set
4. 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.

/*****************************************************************

	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,,,);