I'm working on a class project. Attached is the XLSX file I am working with. On the first project, I wrote the following code:
LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/poverty.xlsx"; LIBNAME Check CLEAR; LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/poverty.xlsx"; DATA CoImpt.Poverty; SET CoImpt.Poverty; RUN; PROC TRANSPOSE DATA=CoImpt.Poverty OUT=CoImpt.tPOVERTY; RUN; PROC PRINT DATA = CoImpt.Poverty; RUN; PROC CONTENTS DATA=coimpt.tPoverty; RUN;
And here is the log for this code:
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/poverty.xlsx"; NOTE: Libref CHECK was successfully assigned as follows: Engine: XLSX Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/poverty.xlsx 70 LIBNAME Check CLEAR; NOTE: Libref CHECK has been deassigned. 71 72 LIBNAMECoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/poverty.xlsx"; NOTE: Libref COIMPT was successfully assigned as follows: Engine: XLSX Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/poverty.xlsx 73 74 DATA CoImpt.Poverty; 75 SET CoImpt.Poverty; 76 RUN; NOTE: The import data set has 0 observations and 1 variables. NOTE: There were 0 observations read from the data set COIMPT.Poverty. NOTE: The data set COIMPT.Poverty has 0 observations and 1 variables. NOTE: The export data set has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.06 seconds user cpu time 0.05 seconds system cpu time 0.01 seconds memory 7988.53k OS Memory 27756.00k Timestamp 11/05/2023 02:37:31 AM Step Count 39 Switch Count 1 Page Faults 0 Page Reclaims 2782 Page Swaps 0 Voluntary Context Switches 41 Involuntary Context Switches 0 Block Input Operations 128 Block Output Operations 184 77 PROC TRANSPOSE DATA=CoImpt.Poverty OUT=CoImpt.tPOVERTY; 78 RUN; NOTE: No variables to transpose. NOTE: The import data set has 0 observations and 1 variables. NOTE: The import data set has 0 observations and 1 variables. NOTE: There were 0 observations read from the data set COIMPT.Poverty. NOTE: The data set COIMPT.tPOVERTY has 0 observations and 1 variables. NOTE: The export data set has 0 observations and 1 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.06 seconds user cpu time 0.05 seconds system cpu time 0.00 seconds memory 7977.31k OS Memory 28012.00k Timestamp 11/05/2023 02:37:32 AM Step Count 40 Switch Count 1 Page Faults 0 Page Reclaims 2378 Page Swaps 0 Voluntary Context Switches 41 Involuntary Context Switches 0 Block Input Operations 176 Block Output Operations 176 78 ! 79 80 PROC PRINT 81 DATA = CoImpt.Poverty; 82 RUN; NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT. NOTE: The import data set has 0 observations and 1 variables. NOTE: No observations in data set COIMPT.Poverty. NOTE: There were 0 observations read from the data set COIMPT.Poverty. NOTE: PROCEDURE PRINT used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 3986.59k OS Memory 23028.00k Timestamp 11/05/2023 02:37:32 AM Step Count 41 Switch Count 0 Page Faults 0 Page Reclaims 783 Page Swaps 0 Voluntary Context Switches 10 Involuntary Context Switches 0 Block Input Operations 48 Block Output Operations 0 83 83 ! PROC CONTENTS DATA=coimpt.tPoverty; 84 RUN; NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.03 seconds user cpu time 0.02 seconds system cpu time 0.01 seconds memory 5223.06k OS Memory 24056.00k Timestamp 11/05/2023 02:37:32 AM Step Count 42 Switch Count 0 Page Faults 0 Page Reclaims 1037 Page Swaps 0 Voluntary Context Switches 2 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 16 85 86 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 96
This worked fine until now, and for some reason, now nothing is showing up in the output data. In the current project, I wrote this code:
PROC PRINT DATA = CoImpt.Poverty; RUN; PROC TRANSPOSE DATA = CoImpt.Poverty; RUN; PROC PRINT DATA = CoImpt.Poverty; RUN;
And here is the log for this code:
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 PROC PRINT DATA = CoImpt.Poverty; 70 RUN; NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT. NOTE: The import data set has 0 observations and 1 variables. NOTE: No observations in data set COIMPT.Poverty. NOTE: There were 0 observations read from the data set COIMPT.Poverty. NOTE: PROCEDURE PRINT used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 3992.46k OS Memory 26052.00k Timestamp 11/05/2023 02:37:37 AM Step Count 48 Switch Count 0 Page Faults 0 Page Reclaims 956 Page Swaps 0 Voluntary Context Switches 13 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 71 72 PROC TRANSPOSEDATA = CoImpt.Poverty; 73 RUN; NOTE: No variables to transpose. NOTE: The import data set has 0 observations and 1 variables. NOTE: The import data set has 0 observations and 1 variables. NOTE: There were 0 observations read from the data set COIMPT.Poverty. NOTE: The data set WORK.DATA3 has 0 observations and 1 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.01 seconds user cpu time 0.02 seconds system cpu time 0.00 seconds memory 4362.15k OS Memory 26312.00k Timestamp 11/05/2023 02:37:37 AM Step Count 49 Switch Count 2 Page Faults 0 Page Reclaims 734 Page Swaps 0 Voluntary Context Switches 17 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 264 74 75 PROC PRINT DATA = CoImpt.Poverty; 76 RUN; NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT. NOTE: The import data set has 0 observations and 1 variables. NOTE: No observations in data set COIMPT.Poverty. NOTE: There were 0 observations read from the data set COIMPT.Poverty. NOTE: PROCEDURE PRINT used (Total process time): real time 0.01 seconds user cpu time 0.02 seconds system cpu time 0.00 seconds memory 3992.46k OS Memory 26052.00k Timestamp 11/05/2023 02:37:37 AM Step Count 50 Switch Count 0 Page Faults 0 Page Reclaims 664 Page Swaps 0 Voluntary Context Switches 2 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 77 78 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 88
It was also working earlier, and now it is not. I don't know why it stopped working, and I don't know how to fix it. There are no errors or messages in the log for either section of code.
I was working on removing the word "FIPS" from each observation after it is transposed, but I am not sure how to do that with a base xlsx file. Any help greatly appreciated.
Why would read from the POVERTY sheet in the XLSX and immediately write back into the exact same sheet in the exact same XLSX file?
DATA CoImpt.Poverty;
SET CoImpt.Poverty;
RUN;
If it worked it would destroy your source data.
What did you think the CHECK libref was "checking"? If you make a libref pointing to an XLSX file SAS is not going to check if the XLSX file exists or not. It does not know if you intended to read something from that file (like the SET statement above) or write something into that file (like the DATA statement above). So the libname statement will succeed if there is no file. The only way it would fail would be if the file exist but it is not a valid XLSX file.
Can you go more into solutions for this? New to SAS and still learning.
The sheet has more than a thousand columns (up to Excel column AUU!), which may crack limits in the XLSX engine.
Did you pivot that in Excel? If yes, import the original table into SAS.
Or, save as csv and try to import that.
For the project, I cannot pivot anything in excel. I am to use PROC TRANSPOSE to do this in SAS. I wrote code for this and it worked fine, until problems in original post occurred.
First thing you need to change: Do not overwrite your source table but create a new table. Normally you create such tables in WORK and only write to a permanent location if you need to keep the result target table after your SAS session terminated.
If "worked fine" means that it returned the desired result then I'm not sure what your desired result should be given below log Note.
Below some code that doesn't overwrite your source data and that also actually transposes the data. I'm of course not sure if the transposed data is in the structure you want it. If not then please show us how the transformed data structure should look like.
libname coimpt xlsx "c:\temp\poverty.xlsx";
data work.poverty_wide;
set coimpt.poverty;
run;
libname coimpt clear;
proc transpose data=work.poverty_wide out=work.poverty_long(rename=(_name_=Fips) drop=_label_);
id _name_;
run;
proc print
data = work.poverty_wide;
run;
proc print
data = work.poverty_long;
run;
proc contents data=work.poverty_long;
run;
Thank you for the response. When I run this code, this is my log (1 warning and 3 errors). I guess a better question is: since I have already made the mistake of overwriting my code within SAS, how do I fix it?
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 *Convert from wide to long using PROC TRANSPOSE*; 70 libname coimpt xlsx "c:\temp\poverty.xlsx"; NOTE: Libref COIMPT was successfully assigned as follows: Engine: XLSX Physical Name: c:\temp\poverty.xlsx 71 72 data work.poverty_wide; 73 set coimpt.poverty; ERROR: File COIMPT.poverty.DATA does not exist. 74 run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.POVERTY_WIDE may be incomplete. When this step was stopped there were 0 observations and 0 variables. WARNING: Data set WORK.POVERTY_WIDE was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 2798.03k OS Memory 31092.00k Timestamp 11/05/2023 11:54:48 PM Step Count 244 Switch Count 0 Page Faults 0 Page Reclaims 652 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 75 76 libname coimpt clear; NOTE: Libref COIMPT has been deassigned. 77 78 proc transpose data=work.poverty_wide out=work.poverty_long(rename=(_name_=Fips) drop=_label_); 79 id _name_; 80 run; NOTE: No variables to transpose. WARNING: The variable _label_ in the DROP, KEEP, or RENAME list has never been referenced. NOTE: There were 0 observations read from the data set WORK.POVERTY_WIDE. NOTE: The data set WORK.POVERTY_LONG has 0 observations and 1 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 977.34k OS Memory 28964.00k Timestamp 11/05/2023 11:54:48 PM Step Count 245 Switch Count 2 Page Faults 0 Page Reclaims 104 Page Swaps 0 Voluntary Context Switches 10 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 264 81 82 proc print 83 data = work.poverty_wide; 84 run; NOTE: No observations in data set WORK.POVERTY_WIDE. NOTE: PROCEDURE PRINT used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 497.34k OS Memory 28704.00k Timestamp 11/05/2023 11:54:48 PM Step Count 246 Switch Count 0 Page Faults 0 Page Reclaims 18 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 85 proc print 86 data = work.poverty_long; 87 run; NOTE: No observations in data set WORK.POVERTY_LONG. NOTE: PROCEDURE PRINT used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 743.28k OS Memory 28964.00k Timestamp 11/05/2023 11:54:48 PM Step Count 247 Switch Count 0 Page Faults 0 Page Reclaims 52 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 88 89 proc contents data=work.poverty_long; 90 run; NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.02 seconds user cpu time 0.02 seconds system cpu time 0.00 seconds memory 1965.65k OS Memory 29224.00k Timestamp 11/05/2023 11:54:48 PM Step Count 248 Switch Count 0 Page Faults 0 Page Reclaims 99 Page Swaps 0 Voluntary Context Switches 2 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 91 92 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 102
@BLT2023 wrote:
Thank you for the response. When I run this code, this is my log (1 warning and 3 errors). I guess a better question is: since I have already made the mistake of overwriting my code within SAS, how do I fix it?
First, I would check the state of your excel file, poverty.xlsx. It looks like it is is stored in:
/home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/poverty.xlsx
Do you know how to copy poverty.xlsx to your PC, so that you can open it in Excel to see the values? That would probably be the easiest way to check if you have accidentally overwritten your data. If the file is essentially empty, you may need to ask a classmate or the professor to provide you with another copy.
Thank you for the suggestion. My data looks just like it should, with all the data intact, so I'm unclear why it is not producing my desired results.
That is good news that your data is still there.
When writing a SAS program, generally you want to write it one step at a time, and test each step.
Your first step reads the data from the Excel sheet into a SAS dataset. But it looks like that step is not working. If you run:
LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/poverty.xlsx";
DATA Poverty;
SET CoImpt.Poverty;
RUN;
What do you see in the log? If you see notes like you posted originally:
NOTE: The import data set has 0 observations and 1 variables. NOTE: There were 0 observations read from the data set COIMPT.Poverty.
that is a problem. And that is the first problem you'll need to solve.
The name of the Excel file is poverty.xlsx. When you open the Excel file, what is the name of the worksheet that has the data? It's also named "Poverty"? Or is it named the default "Sheet1". Or something else?
Here is the log based on your provided code. It is as you predicted. Also, yes, the sheet is also named poverty in the excel file.
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68 69 LIBNAMECoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/poverty.xlsx"; NOTE: Libref COIMPT was successfully assigned as follows: Engine: XLSX Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/poverty.xlsx 70 71 DATA Poverty; 72 SET CoImpt.Poverty; 73 RUN; NOTE: The import data set has 0 observations and 1 variables. NOTE: There were 0 observations read from the data set COIMPT.Poverty. NOTE: The data set WORK.POVERTY has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds user cpu time 0.02 seconds system cpu time 0.00 seconds memory 4694.50k OS Memory 36628.00k Timestamp 11/06/2023 12:56:28 AM Step Count 413 Switch Count 2 Page Faults 0 Page Reclaims 1106 Page Swaps 0 Voluntary Context Switches 21 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 160 74 75 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 85
Sorry, I don't have any great idea. You might try using a different engine to import the data, e.g.
LIBNAME CoImpt excel "&CourseRoot/CDPHE Study/Data/1_Source/poverty.xlsx";
You might also try calling SAS tech support to see if they can help.
I haven't looked in the file, but @Kurt_Bremser said it has 1000+ columns? Just as a test, you might make a copy of the file, and delete most of the columns, and then try to code again. If the DATA step can read the file copy with fewer columns, that would confirm that the number of columns is a problem.
But I've read some Excel files with >1000 columns (unfortunately), and never had this problem.
It looks like SAS really things there are now rows in the poverty worksheet, and just a single column.
Thank you for all of your help! I really appreciate it.
What is the name of the single variable in your dataset?
The single variable is _NAME_
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.