Accepted Solutions
That is not a very friendly way to share data. The workbook has three sheets. Two of them have names that are not valid SAS names. And the third is completely empty.
915 libname xx xlsx "C:\downloads\Master Compiled Spreadsheet 09-14-2022 Kelly edit dates only.xlsx";
NOTE: Libref XX was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\downloads\Master Compiled Spreadsheet 09-14-2022 Kelly edit dates only.xlsx
916 proc copy inlib=xx outlib=work;
917 run;
NOTE: Copying XX.CPT 23472 to WORK.CPT 23472 (memtype=DATA).
NOTE: Variable Name Change. Immediate postop XR findings -> Immediate_postop_XR_findings
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
ERROR: The value CPT 23472 is not a valid SAS name.
ERROR: File WORK.'CPT 23472'n.DATA has not been saved because copy could not be completed.
NOTE: Copying XX.MYBI2.CUMEDICINE.US BISM SERVIC to WORK.MYBI2.CUMEDICINE.US BISM SERVIC (memtype=DATA).
NOTE: Variable Name Change. 23472 -> _23472
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
ERROR: The value MYBI2.CUMEDICINE.US BISM SERVIC is not a valid SAS name.
ERROR: File WORK.'MYBI2.CUMEDICINE.US BISM SERVIC'n.DATA has not been saved because copy could not be completed.
NOTE: Copying XX.SHEET1 to WORK.SHEET1 (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: The import data set has 0 observations and 0 variables.
NOTE: There were 0 observations read from the data set XX.SHEET1.
NOTE: The data set WORK.SHEET1 has 0 observations and 0 variables.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.06 seconds
cpu time 0.04 seconds
NOTE: The SAS System stopped processing this step because of errors.
If you set the VALIDMEMNAME option to EXTEND you can use name literals for member names. T
Let's check out that first sheet to see what is going on.
proc contents data=xx.'CPT 23472'n varnum; run;You seem to have 3 variables that from their names appear like they should have date values, but instead are character strings.
# Variable Type Len Format Informat Label 1 postop_XR_date Char 8 $8. $8. postop_XR_date 2 Immediate_postop_XR_findings Char 640 $640. $640. Immediate postop XR findings 3 Second_Xray_date Char 5 $5. $5. Second_Xray_date 4 Date_res_of_gas Char 5 $5. $5. Date_res_of_gas 5 Infection Char 2 $2. $2. Infection
So let's look at a few of the values to see what is happening. No need to look at all 600+ characters of that long string.
proc print data=xx.'CPT 23472'n(obs=10) width=min;
format _character_ $32. ;
run;Result:
Second_ Date_
postop_ Xray_ res_of_
Obs XR_date Immediate_postop_XR_findings date gas Infection
1 42604 Reverse total shoulder arthropla 42620 N
2 43633 Re-redemonstration right reverse 43649 N
3 43115 A reverse left total shoulder pr 43152 N
4 43297 Compared with December 7, 2017. 43327 N
5 43560 Interval reverse shoulder arthro 43574 43602 N
6 42506 New reverse total shoulder prost 42517 N
7 43724 Interval right shoulder arthropl 43739 N
8 42580 A reverse right total shoulder p 42592 N
9 42723 There is been interval placement 42734 N
10 42681 There is been interval placement 42690 N
So it clearly looks like some of the cells in those three columns have TEXT instead of DATE values. So when SAS converted the sheet into a dataset it had to make the variable CHARACTER to handle those few cells with text in them. When it does that it stores the actual number Excel uses to represent the date values as a raw digit string. Hence the 5 digit strings we see in the first 10 observations.
So let's see what type of gibberish is confusing excel and SAS about what type of variables those should be.
proc print data=xx.'CPT 23472'n(obs=10) width=min;
where not (lengthn(postop_XR_date) in (0,5))
or not (lengthn(Second_Xray_date) in (0,5))
or not (lengthn(Date_res_of_gas) in (0,5))
;
format _character_ $32. ;
run;So the first one has '1/29/18'. That looks like a date to me. Perhaps whoever built the spreadsheet is in a region that prefers to enter dates in DMY order instead. If so then there is no month number 29.
The second one has values like 'NONE'. That is NOT a date in any shape.
The third one has values like 'N/A' or 'NA'. Neither of those is a date either.
Fixing the second two looks simple. Just convert the string to a number and add '30DEC1899'd to adjust for the difference in offset date. (Do NOT use magic numbers like the one in your post, how can anyone reading that understand why you used that particular number?)
For the first one you might want to add some more logic to check if the string is actually in MDY style instead.
data want;
set xx.'CPT 23472'n ;
array dates postop_XR_date Second_Xray_date Date_res_of_gas ;
array daten postop_XR_date_n Second_Xray_date_n Date_res_of_gas_n ;
do over dates;
daten=input(dates,??5.);
if not missing(daten) then daten=daten+'30DEC1899'd ;
else daten=input(dates,??mmddyy10.);
end;
format postop_XR_date_n Second_Xray_date_n Date_res_of_gas_n yymmdd10.;
run;
proc print data=want (obs=10) width=min;
format _character_ $32.;
run;
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
-
4881 views
-
4 likes
-
-
