i read a excel file using proc import the file has-->dataset name, variable name, variable type, variable length,I need to read that information and write a code to create empty dataset(Macro). thanks in advance. here is my import code
proc import datafile="C:\mymacros" ;
out=&out_dat dbms=xls
replace;
sheet="&exl_sht";
getnames=yes;
mixed=yes;
run;
Hi,
Once again, what you are asking for is for someone to create a metadata driven programming environment for you. This is a question and answer forum not a contract work order site. I can give some tips for you to start:
1) Don't use Excel, it really is just going to cause you lots of problems.
2) Proc sql is simplest to create empty tables as it doesn't create the one record like a datastep would:
proc sql;
create table WANT (AVAR1 char(20),AVAR2 num);
quit;
3) To generate code you would use one of two methods, either you put the information out to a text file, i.e. from your metadata you would create a text SAS program which then gets included at the end. The other method is by using the call execute function in a data _null_ step to generate the required code.
Note, that this is an advanced topic.
I don't know the structure of your metadata, but for an example I'll use what SAS Enterprise Guide can give you when you copy the column attributes.
Here's an example of CLASSFIT metadata in DATA step form.
/* A data step that defines some metadata */
data meta;
length
dsname $ 32 /* data set name */
name $ 32 /* var name */
type $ 1 /* var type */
length 8
format $ 15
informat $ 15
label $ 255;
infile datalines dsd delimiter=',';
input dsname name type length format informat label;
datalines;
classfit,Name,Character,8,,,
classfit,Sex,Character,1,,,
classfit,Age,Numeric,8,,,
classfit,Height,Numeric,8,,,
classfit,Weight,Numeric,8,,,
classfit,predict,Numeric,8,,,Predicted Value of Weight
classfit,lowermean,Numeric,8,,,Lower Bound of 95% C.I. for Mean
classfit,uppermean,Numeric,8,,,Upper Bound of 95% C.I. for Mean
classfit,lower,Numeric,8,,,Lower Bound of 95% C.I.(Individual Pred)
classfit,upper,Numeric,8,,,Upper Bound of 95% C.I.(Individual Pred)
;
run;
Okay, so how to read that in and create an empty data set with just the columns in place? There are a bunch of different approaches. My method here is to use DATA step to read the metadata, then output a file with a new DATA step that uses the ATTRIB statement to build up the definitions.
/* Create a temp file to hold a SAS program */
filename datadef temp;
data _null_;
file datadef;
set meta nobs=last;
if _N_=1 then
put "DATA " dsname "; ATTRIB ";
put name " LENGTH= ";
if type='C' then
put "$" length;
else put length;
if format^= '' then
put " FORMAT=" format;
if informat^= '' then
put " INFORMAT=" informat;
/* if the LABEL contains quote chars, going to have to escape those */
put " LABEL='" label "'";
if _N_ = last then
put "; STOP;RUN;";
run;
/* INCLUDE and run the program */
%include datadef;
The program that is created and run for SASHELP.CLASSFIT (you don't see the code) looks like this. Formatting isn't lovely, but SAS appreciates only that the syntax is correct, not that your code is human-readable.
DATA classfit ; ATTRIB
Name LENGTH=
$8
LABEL=' '
Sex LENGTH=
$1
LABEL=' '
Age LENGTH=
8
LABEL=' '
Height LENGTH=
8
LABEL=' '
Weight LENGTH=
8
LABEL=' '
predict LENGTH=
8
LABEL='Predicted Value of Weight '
lowermean LENGTH=
8
LABEL='Lower Bound of 95% C.I. for Mean '
uppermean LENGTH=
8
LABEL='Upper Bound of 95% C.I. for Mean '
lower LENGTH=
8
LABEL='Lower Bound of 95% C.I.(Individual Pred) '
upper LENGTH=
8
LABEL='Upper Bound of 95% C.I.(Individual Pred) '
; STOP;RUN;
Don't forget to put the TEMP keyword on the filename statement:
filename myexcel TEMP;
The TEMP tells SAS to create a temp file in the WORK area; you don't need to worry about the physical file name.
And include the FILE statement in your DATA step to tell the PUT statement to direct output to the file. Example:
data _null_; file MYEXCEL; /* remainder of program */
Chris
You havea fair few amount of problems in that code, how are you testing it? Some pointers:
NOTE: Variable Name Change. VAR_FORMAT (Optional) -> VAR10
This is telling you that the variable VAR_FORMAT was changed to VAR10, hence why you get missings for format later on. In my opionion, Excel is NOT a metadata tool and the use of it for such a thing will result in so many problems it is untrue, much like the one you have found above. As I previously mentioned, get a contractor who can advise on a proper metadata repository/driven programming rather than trying to piece it together with things like Excel which will just break everytime you riun it.
NOTE: 0 records were written to the file MYEXCEL.What is this block of code supposed to be doing, either there is part of the program missing, or something is wrong. You have a filename (66), data _null_ (67), and file (68), however there is no further code, hence the file created is empty. Where are all the put statements and the ending run;?
NOTE: Variable var_format is uninitialized.
This again is telling you that var_format does not exist in your dataset, see the first comment about reading in from Excel.
75 put 'data ' dataset_name;
This is incorrect. It needs to finish with a semicolon:
75 put cat('data ',strip(dataset_name),';');
92 %include myexcel;
This isn't doing anything as the file is empty.
I would suggest you firstly try to understand what Chris has suggested you try, basically from your code you are generating another piece of code which finally gets included, this is what the basis of metadata driven programming is. Once you understand the concept, then go through and test your code, step by step, look at the datasets created, the files created, you will see straight away what the problems are.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.