here is my Code--> 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 ... View more

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

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 ... View more

@RW9 filename was wrong ut there is another issue..... 66 filename myexcel temp; 67 data _null_; 68 file myexcel; 69 set WORK.MYEXCEL end=last; 70 by dataset_name; 71 length statement_str $1000.; 72 if first.dataset_name then do; 73 put 'data ' dataset_name; 74 end; 75 statement_str = 'ATTRIB '|| var_name||' label = "'|| trim(var_label) ||'"'; 76 if trim(var_format) ne " " then do; 77 put ' ' statement_str ' format = ' var_format ';'; 78 end; 79 else do; 80 put ' ' statement_str ';'; 81 end; 82 if last.dataset_name then do; 83 put 'run;'; 84 end; 85 run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 76:9 NOTE: Variable var_format is uninitialized. NOTE: The file MYEXCEL is: Filename=/tmp/SAS_work95AA00000F92_localhost.localdomain/#LN00313, Owner Name=sasdemo,Group Name=sas, Access Permission=-rw-rw-r--, Last Modified=18Dec2015:17:13:37 NOTE: 31 records were written to the file MYEXCEL. The minimum record length was 4. The maximum record length was 78. NOTE: There were 29 observations read from the data set WORK.MYEXCEL. NOTE: DATA statement used (Total process time): real time 0.09 seconds cpu time 0.08 seconds 86 %include myexcel; 88 + ATTRIB STUDYID label = "Study Identifier" format = . ; _ _ 22 22 200 76 89 + ATTRIB DOMAIN label = "Domain Abbreviation" format = . ; _ 85 76 90 + ATTRIB USUBJID label = "Unique Subject Identifier" format = . ; _ 85 76 91 + ATTRIB SUBJID label = "Subject Identifier for the Study" format = . ; _ 85 76 92 + ATTRIB SITEID label = "Study Site Identifier" format = . ; _ 85 76 93 + ATTRIB RFSTDTC label = "Subject Reference Start Date/Time" format = . ; _ 85 76 94 + ATTRIB RFENDTC label = "Subject Reference End Date/Time" format = . ; _ 85 76 95 + ATTRIB RFXSTDTC label = "Date/Time of First Study Treatment" format = . ; _ 85 76 96 + ATTRIB RFXENDTC label = "Date/Time of Last Study Treatment" format = . ; _ 85 76 97 + ATTRIB RFICDTC label = "Date/Time of Informed Consent" format = . ; _ 85 76 98 + ATTRIB RFPENDTC label = "Date/Time of End of Participation" format = . ; _ 85 76 99 + ATTRIB DTHDTC label = "Date/Time of Death" format = . ; _ 85 76 100 + ATTRIB DTHFL label = "Subject Death Flag" format = . ; _ 85 76 101 + ATTRIB INVID label = "Investigator Identifier" format = . ; _ 85 76 102 + ATTRIB INVNAM label = "Investigator Name" format = . ; _ 85 76 103 + ATTRIB BRTHDTC label = "Date/Time of Birth" format = . ; _ 85 76 104 + ATTRIB AGE label = "Age" format = . ; _ 85 76 105 + ATTRIB AGEU label = "Age Units" format = . ; _ 85 76 106 + ATTRIB SEX label = "Sex" format = . ; _ 85 76 107 + ATTRIB RACE label = "Race" format = . ; _ 85 76 108 + ATTRIB ETHNIC label = "Ethnicity" format = . ; _ 85 76 109 + ATTRIB ARMCD label = "Planned Arm Code" format = . ; _ 85 76 110 + ATTRIB ARM label = "Description of Planned Arm" format = . ; _ 85 76 111 + ATTRIB ACTARMCD label = "Actual Arm Code" format = . ; _ 85 76 112 + ATTRIB ACTARM label = "Description of Actual Arm" format = . ; _ 85 76 113 + ATTRIB COUNTRY label = "Country" format = . ; _ 85 76 114 + ATTRIB DMDTC label = "Date/Time of Collection" format = . ; _ 85 76 115 + ATTRIB DMDY label = "Study Day of Collection" format = . ; _ 85 76 116 + ATTRIB RACEOTH label = "Race, other" format = . ; _ 85 76 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_. ERROR 200-322: The symbol is not recognized and will be ignored. ERROR 76-322: Syntax error, statement will be ignored. ERROR 85-322: Expecting a format name. ... View more

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

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

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.. ... View more

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? ... View more

can we edit this code to display attribute of the dataset variable... ... View more

@dcruik i think my code is wrong i do not want to display numeric attributes of a variable but i want to display the variable itself. 56 %macro datatt(data=, 57 attribute=, 58 type=, 59 notfound=, 60 attrname=); 61 %local dsid; 62 63 %if %length(&type)= 0 %then %let type= N; 64 %else %let type = %upcase(%substr(&type,1,1)); 65 %if &type = N %then %let notfound = .; 66 %else %let notfound = ' '; 67 68 %let dsid = %sysfunc(open(&data)); 69 %if &dsid ne 0 %then 70 %put variable=%sysfunc(attrn(&dsid,&attrname)); 71 %else 72 73 %put &notfound; 74 %let dsid = close(&dsid); 75 76 %mend datatt; 77 %datatt(data=sashelp.class, attribute=, type=n, notfound=., attrname=nvars); variable=5---> a specific variable name should be displayed.. ... View more

no no u get me wrong @RW9 i know what am i doing i know macros am jst asking suggestions for my doubts & i send this doubt here for some insight....@rw9 so .... ... View more

nope am understanding macros as am working on it.... ... View more

@RW9 @jagdish can u provide a specific generic macro code that adds quotes around each item in a list. This list can consist of individual items or a previously defined macro variable containing values?? ... View more