topic Re: read excel file &create empty dataset. in SAS Procedures

read excel file &create empty dataset.

RTelang — Thu, 17 Dec 2015 09:54:42 GMT

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;

Re: read excel file &create empty dataset.

RW9 — Thu, 17 Dec 2015 10:31:14 GMT

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.

Re: read excel file &create empty dataset.

RTelang — Thu, 17 Dec 2015 10:42:37 GMT

k my question is lil wrong i need a logic to read the info from the excel sheet & read data &then create the dataset how can i do this?

Re: read excel file &create empty dataset.

RTelang — Thu, 17 Dec 2015 11:02:31 GMT

my new import code

56 PROC IMPORT DATAFILE="/folders/myfolders/sasuser.v94/GSK Vx-FORM-SDTM-Metadata-Extension-Form_v2.0.xlsx"
57 OUT=WORK.MYEXCEL
58 DBMS=XLSX
59 REPLACE;
60 sheet="Structural_Metadata";
61 GETNAMES=YES;
62 RUN;

NOTE: Variable Name Change. VAR_FORMAT (Optional) -> VAR10
NOTE: The import data set has 29 observations and 13 variables.
NOTE: WORK.MYEXCEL data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.15 seconds
cpu time 0.15 seconds

can i use atrib & read the info & create a empty dataset.. how would i go with the logic..

Re: read excel file &create empty dataset.

ChrisHemedinger — Thu, 17 Dec 2015 13:46:50 GMT

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;

Re: read excel file &create empty dataset.

Reeza — Thu, 17 Dec 2015 15:34:43 GMT

Unfortunately this outputs to the log, but you may be able to capture it somehow:

proc sql;
describe table sashelp.class;
quit;

Re: read excel file &create empty dataset.

RTelang — Fri, 18 Dec 2015 10:44:39 GMT

@ chris i am getting error at file-name & %include statement--->physical file does not exist in my code....

Re: read excel file &create empty dataset.

RTelang — Fri, 18 Dec 2015 10:48:28 GMT

I get this error messege......
WARNING: Physical file does not exist, /opt/sasinside/SASConfig/Lev1/SASApp/myexcel.sas.
ERROR: Cannot open %INCLUDE file MYEXCEL.

Re: read excel file &create empty dataset.

ChrisHemedinger — Fri, 18 Dec 2015 11:47:56 GMT

You'll have to share your entire log in order to see what's going on.

Re: read excel file &create empty dataset.

ChrisHemedinger — Fri, 18 Dec 2015 12:41:23 GMT

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

Re: read excel file &create empty dataset.

RTelang — Thu, 24 Dec 2015 06:39:40 GMT

here is my entire log--->

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
55
56 proc import datafile="/folders/myfolders/sasuser.v94/GSK Vx-FORM-SDTM-Metadata-Extension-Form_v2.0.xlsx"
57 out=WORK.MYEXCEL
58 dbms=XLSX
59 replace;
60 sheet="Structural_Metadata";
61 getnames=YES;
62 run;

NOTE: Variable Name Change. VAR_FORMAT (Optional) -> VAR10
NOTE: The import data set has 29 observations and 13 variables.
NOTE: WORK.MYEXCEL data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.11 seconds
cpu time 0.11 seconds

63
64 PROC PRINT DATA=WORK.MYEXCEL;
65 RUN;

NOTE: There were 29 observations read from the data set WORK.MYEXCEL.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.58 seconds
cpu time 0.57 seconds

66 filename myexcel temp;
67 data _null_;
68 file myexcel;

NOTE: The file MYEXCEL is:
Filename=/tmp/SAS_workFA0E00000759_localhost.localdomain/#LN00179,
Owner Name=sasdemo,Group Name=sas,
Access Permission=-rw-rw-r--,
Last Modified=24Dec2015:12:08:43

NOTE: 0 records were written to the file MYEXCEL.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.07 seconds

69 data _null_;

70 set work.myexcel;
71 by dataset_name;
72 length statement_str $1000.;
73
74 if first.dataset_name then do;
75 put 'data ' dataset_name;
76 end;
77
78 statement_str = 'ATTRIB '|| var_name||' label = "'|| trim(var_label) ||'"';
79
80 if trim(var_format) ne " " then do;
81 put ' ' statement_str ' format = ' var_format ';';
82 end;
83 else do;
84 put ' ' statement_str ';';
85 end;
86
87 if last.dataset_name then do;
88 put 'run;';
89 end;
90 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
80:9
NOTE: Variable var_format is uninitialized.
data DM
ATTRIB STUDYID label = "Study Identifier" format = . ;
ATTRIB DOMAIN label = "Domain Abbreviation" format = . ;
ATTRIB USUBJID label = "Unique Subject Identifier" format = . ;
ATTRIB SUBJID label = "Subject Identifier for the Study" format = . ;
ATTRIB SITEID label = "Study Site Identifier" format = . ;
ATTRIB RFSTDTC label = "Subject Reference Start Date/Time" format = . ;
ATTRIB RFENDTC label = "Subject Reference End Date/Time" format = . ;
ATTRIB RFXSTDTC label = "Date/Time of First Study Treatment" format = . ;
ATTRIB RFXENDTC label = "Date/Time of Last Study Treatment" format = . ;
ATTRIB RFICDTC label = "Date/Time of Informed Consent" format = . ;
ATTRIB RFPENDTC label = "Date/Time of End of Participation" format = . ;
ATTRIB DTHDTC label = "Date/Time of Death" format = . ;
ATTRIB DTHFL label = "Subject Death Flag" format = . ;
ATTRIB INVID label = "Investigator Identifier" format = . ;
ATTRIB INVNAM label = "Investigator Name" format = . ;
ATTRIB BRTHDTC label = "Date/Time of Birth" format = . ;
ATTRIB AGE label = "Age" format = . ;
ATTRIB AGEU label = "Age Units" format = . ;
ATTRIB SEX label = "Sex" format = . ;
ATTRIB RACE label = "Race" format = . ;
ATTRIB ETHNIC label = "Ethnicity" format = . ;
ATTRIB ARMCD label = "Planned Arm Code" format = . ;
ATTRIB ARM label = "Description of Planned Arm" format = . ;
ATTRIB ACTARMCD label = "Actual Arm Code" format = . ;
ATTRIB ACTARM label = "Description of Actual Arm" format = . ;
ATTRIB COUNTRY label = "Country" format = . ;
ATTRIB DMDTC label = "Date/Time of Collection" format = . ;
ATTRIB DMDY label = "Study Day of Collection" format = . ;
ATTRIB RACEOTH label = "Race, other" format = . ;
run;
NOTE: There were 29 observations read from the data set WORK.MYEXCEL.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.06 seconds

91 ;
92 %include myexcel;
93
94
95
96 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
108

Re: read excel file &create empty dataset.

RTelang — Thu, 24 Dec 2015 06:50:18 GMT

& 1 question can't i not use temp & create a physical file in diff location...

Re: read excel file &create empty dataset.

RW9 — Thu, 24 Dec 2015 09:40:24 GMT

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.

Re: read excel file &create empty dataset.

Reeza — Thu, 24 Dec 2015 20:19:42 GMT

Theres also the LIKE statement in proc sql.

Proc sql;
Create table want like sashelp.class;
Quit;

Re: read excel file &create empty dataset.

Reeza — Thu, 24 Dec 2015 20:21:37 GMT

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001384767.htm