What happened to my data?

[BLT2023]

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:

Spoiler

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:

Spoiler

 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:

Spoiler

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:

Spoiler

 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. 

[Tom]

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.

[BLT2023]

Can you go more into solutions for this? New to SAS and still learning.

[Kurt_Bremser]

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.

[BLT2023]

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. 

[Patrick]

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;

[BLT2023]

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?

Spoiler

 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        

[Quentin]

---

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

[BLT2023]

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.

[Quentin]

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?

[BLT2023]

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.

Spoiler

 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         

[Quentin]

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.

[BLT2023]

Thank you for all of your help! I really appreciate it. 

[Kurt_Bremser]

What is the name of the single variable in your dataset?

[BLT2023]

The single variable is _NAME_