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" ;
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:
create table WANT (AVAR1 char(20),AVAR2 num);
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 */
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.
SAS is headed back to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team.
Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!
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.