topic Re: Creating a macro that creates tables with a CSV file that contains its columns in SAS Programming

Creating a macro that creates tables with a CSV file that contains its columns

shindeeen — Mon, 26 Sep 2022 04:10:22 GMT

Hello everyone,

Is there a way to create a macro that creates a table which looks up to a CSV file to get its columns with data type, length, format and informat?

CSV file looks like this:

libname,dataset,colname,datatype,length,format,informat
VEHICLES,CARS,TYPE,char,20,$20.,$20.
VEHICLES,CARS,BRAND,char,30,$30.,$30.
VEHICLES,CARS,MODEL,char,30,$30.,$30.
VEHICLES,CARS,PRICE,num,8,BEST32.,BEST32.

Re: Creating a macro that creates tables with a CSV file that contains its columns

Patrick — Mon, 26 Sep 2022 09:48:00 GMT

Before you start writing SAS macros implement what you need for a single use case not using macro language. Once that works you can make things dynamic by "macrotizing" your code.

You certainly can read the .csv as per your screenshot into a SAS table. Once that's done what do you want to do with it?

Re: Creating a macro that creates tables with a CSV file that contains its columns

Ksharp — Mon, 26 Sep 2022 11:49:57 GMT

option parmcards=x;
filename x temp;
parmcards;
libname,dataset,colname,datatype,length,format,informat
VEHICLES,CARS,TYPE,char,20,$20.,$20.
VEHICLES,CARS,BRAND,char,30,$30.,$30.
VEHICLES,CARS,MODEL,char,30,$30.,$30.
VEHICLES,CARS,PRICE,num,8,BEST32.,BEST32.
;





proc import datafile=x out=dictionary dbms=csv replace;
run;
data _null_;
 set dictionary end=last;
 by libname dataset;
 if _n_=1 then call execute('proc sql;');
 if first.dataset then call execute(cat('create table ',dataset,' ('));
 call execute(cat(colname,' ',datatype,'(',length,') format=',format,' informat=',informat));
 if not last.dataset then call execute(',');
  else call execute(');');
 if last then call execute(';quit;');
run;

Re: Creating a macro that creates tables with a CSV file that contains its columns

Tom — Mon, 26 Sep 2022 14:31:07 GMT

A CSV file does not have any of the items you want.

Just look at it what you posed. The only metadata it has is the first line, which most people use as the NAME of the variables.

The TYPE or storage LENGTH or whether or not you need to use an INFORMAT to read the string from the CSV or not, or whether or not you need to attach a FORMAT to display the values in a way that humans would like to see is NOT provided.

The best you could do is GUESS what values could work. But knowing what the creator of the file intended is not possible.

Ask the file creator to provide the metadata you want in a separate file.

If you must GUESS you could try using PROC IMPORT. Or write your own logic, such as https://github.com/sasutils/macros/blob/master/csv2ds.sas