Re: error help

RTelang · Posted 12-18-2015 04:59 AM

filename myexcel temp;

57 data _null_;

58 set WORK.MYEXCEL;

59 by dataset_name;

60 length statement_str $1000.;

61 if first.dataset_name then do;

62 put 'data ' dataset_name;

63 end;

64 statement_str = 'ATTRIB '|| var_name||' label = "'|| trim(var_label) ||'"';

65 if trim(var_format) ne " " then do;

66 put ' ' statement_str ' format = ' var_format ';';

67 end;

68 else do;

69 put ' ' statement_str ';';

70 end;

71 if last.dataset_name then do;

72 put 'run;';

73 end;

74 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).

65: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.04 seconds

cpu time 0.03 seconds

75 %include myexcel;

WARNING: Physical file does not exist, /tmp/SAS_work51B500000BFB_localhost.localdomain/#LN00277.

ERROR: Cannot open %INCLUDE file MYEXCEL.

here is my code where i have imported a work sheet andread the information to create a empty dataset but am getting the waring &error y so??? plz can u'll help....

RW9 · Posted 12-18-2015 06:37 AM

The log tells you all the information you need to know:

NOTE: Numeric values have been converted to character values at the places given by: (Line)

Column).

65:9

NOTE: Variable var_format is uninitialized.

These two are related. In the dataset WORK.MYEXCEL there is no variable called VAR_FORMAT. Hence you have the note as a missing is placed there, and the unitialised, look at the generated code:

ATTRIB STUDYID label = "Study Identifier" format = . ;

You see the . after format, that is a missing.

Secondly you have a typo in the generated code:

data DM

ATTRIB STUDYID...

You will note the missing semicolon after DM.

As for this error:

75 %include myexcel;

WARNING: Physical file does not exist, /tmp/SAS_work51B500000BFB_localhost.localdomain/#LN00277.

ERROR: Cannot open %INCLUDE file MYEXCEL.

What software are you using? That path doesn't look right at all to me, is the university edition? Point the filename statement at the start to an actual physical location, e.g. filename "c:\myexecl.sas"; Once you have the file in an accessible location it should work.

Do note also that this method will create a dataset with one blank row, this is datastep functionality, which is why I suggested proc sql in the other post.

Code for datastep should look something like:

filename myexcel "c:\temp.sas";
data _null_;
  set work.myexcel end=last;
  length statement_str $1000.;
  if _n_=1 then put cat('data ',datasetname,';');
  if trim(var_format) ne " " then put cat('attrib ',varname,' label="',trim(var_label),'" format=',var_format,';');
  if last then put 'run;';
  end;
run;

You may need a switch on the second if depending on what var_format is, i.e. difference between numeric and character formats, however if you have set your metadata file (the excel file), then this shouldn't be a problem.

RTelang · Posted 12-18-2015 06:45 AM

@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.

Reeza · Posted 12-18-2015 11:03 AM

Post your code, not log. I can't see what dataset your trying to apply your attribute statements to? If it's for the outputted file, you need it in the data step above, before the run.

RTelang · Posted 12-24-2015 02:11 AM

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

RTelang · Posted 12-24-2015 02:13 AM

1 question can't i not use temp & create a physical file some location ??

RW9 · Posted 12-24-2015 04:41 AM

Duplicate post:

https://communities.sas.com/t5/SAS-Procedures/read-excel-file-amp-create-empty-dataset/m-p/240774#M5...

RTelang · Posted 12-24-2015 04:48 AM

error when ran ur code

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.12 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.57 seconds

cpu time 0.57 seconds

66

67 filename myexcel "c:\temp.sas";

68 data _null_;

69 set work.myexcel end=last;

70 length statement_str $1000.;

71 if _n_=1 then put cat('data ',datasetname,';');

_______

22

76

72 if trim(var_format) ne " " then put cat('attrib ',varname,' label="',trim(var_label),'" format=',var_format,';');

_________

22

76

ERROR 22-322: Syntax error, expecting one of the following: a name, arrayname, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 76-322: Syntax error, statement will be ignored.

73 if last then put 'run;';

74 end;

___

161

ERROR 161-185: No matching DO/SELECT statement.

75 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).

72:9

NOTE: The SAS System stopped processing this step because of errors.

NOTE: DATA statement used (Total process time):

real time 0.05 seconds

cpu time 0.06 seconds

76

77

78 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

90

Classroom Training Available!