topic Re: What happened to my data? in New SAS User

What happened to my data?

BLT2023 — Sun, 05 Nov 2023 02:40:43 GMT

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.

Re: What happened to my data?

Tom — Sun, 05 Nov 2023 03:47:32 GMT

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.

Re: What happened to my data?

BLT2023 — Sun, 05 Nov 2023 04:28:01 GMT

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

Re: What happened to my data?

Kurt_Bremser — Sun, 05 Nov 2023 07:53:59 GMT

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.

Re: What happened to my data?

BLT2023 — Sun, 05 Nov 2023 22:41:34 GMT

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.

Re: What happened to my data?

Patrick — Sun, 05 Nov 2023 23:45:19 GMT

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;

Re: What happened to my data?

BLT2023 — Sun, 05 Nov 2023 23:56:45 GMT

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

Re: What happened to my data?

Quentin — Mon, 06 Nov 2023 00:15:03 GMT

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

Re: What happened to my data?

BLT2023 — Mon, 06 Nov 2023 00:41:24 GMT

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.

Re: What happened to my data?

Quentin — Mon, 06 Nov 2023 00:53:29 GMT

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?

Re: What happened to my data?

BLT2023 — Mon, 06 Nov 2023 00:58:37 GMT

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

Re: What happened to my data?

Quentin — Mon, 06 Nov 2023 01:09:07 GMT

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.

Re: What happened to my data?

BLT2023 — Mon, 06 Nov 2023 01:11:32 GMT

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

Re: What happened to my data?

Patrick — Mon, 06 Nov 2023 03:01:20 GMT

The code I've shared worked on my laptop with the Excel you provided. Please make sure that your real Excel has the same name (workbook and sheet including casing) and resides under a valid path accessible by your SAS session. If that's the case then the code should work as is.

Re: What happened to my data?

BLT2023 — Mon, 06 Nov 2023 04:32:52 GMT

I have checked all of these things, and it is still not working. I cannot figure out why this would be the case

Re: What happened to my data?

Kurt_Bremser — Mon, 06 Nov 2023 09:17:01 GMT

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

Re: What happened to my data?

Kurt_Bremser — Mon, 06 Nov 2023 09:18:24 GMT

And please don't use the "Spoiler" button. Use only this button for posting logs:

Re: What happened to my data?

Tom — Mon, 06 Nov 2023 13:42:50 GMT

So the file is not where you think it is. Or the file that is there is not the one you want.

Is your version of SAS really running on a Windows machine where a filename that starts with C:\ would make sense? Is it the same machine where you can see the file? If you are running SAS on a Windows server then C: on that server is not the same as C: on your PC.

If you used this code on Unix

libname coimpt xlsx "c:\temp\poverty.xlsx";

It would point COIMPT libref to the file named "c:temppoverty.xlsx" in the current working directory since \ is an escape character to Unix and filenames that do not start with the root node, / , are relative and not absolute.

The LIBRNAME statement will work fine whether or not such a file existed. SAS does not know if you are making the libref COIMPT so that you can begin creating a NEW file or if you are intending to read from an existing file.

The file not existing would explain the notes in your next step:

 72         data work.poverty_wide;
 73           set coimpt.poverty;
 ERROR: File COIMPT.poverty.DATA does not exist.
 74         run;

Or if the file does exist but it does not have a sheet with a name like POVERTY.

Re: What happened to my data?

BLT2023 — Mon, 06 Nov 2023 16:08:13 GMT

The single variable is _NAME_

Re: What happened to my data?

Reeza — Mon, 06 Nov 2023 16:12:36 GMT

Are you sure this is your original file? It looks like the output from a proc transpose not exactly what I would expect as a raw data file.