What happened to my data?

[ballardw]

---

@BLT2023 wrote:

The single variable is _NAME_

---

Sure looks like the result of Proc Transpose.

If I run this code (and you should be able to as well as SASHELP.CLASS is one of the training sets provided by SAS):

proc transpose data=sashelp.class out=trans;
run;

Then the first variable in the output set is named _NAME_.

In this case the result is three rows for the three numeric variables in the SASHELP.Class set. To generate a "No variables to transpose" note the input set would have to have no numeric variables.

Example:

data junk ;
   input x $;
datalines;
abc
pdq
xyzzzz
;

proc transpose data=junk out=junktrans;
run;

But if I specify the name of a character variable to transpose that not of "No variables" does not appear.

proc transpose data=junk out=junktrans;
  var x;
run;

[Kurt_Bremser]

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

[Patrick]

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.

[BLT2023]

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

[Tom]

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.

[BLT2023]

I am working with SAS on a Mac, and I am using the free SAS On Demand for Academics. I have only used this computer when working on this project, and have run the code and viewed the files fine.

Additionally, when I ran the code I switched 

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

For my actual CoImpt libname: 

LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/poverty.xlsx";

And it still did not function correctly. When I download the excel file from SAS and open it on my computer, it looks like it should. When I view the data within SAS, it looks like it should. However, when I tried to run any kind of code on it, it only shows me one variable: _NAME_. Everything else does not appear, no matter what the code is.

[Tom]

If you are using that macro variable refence when defining the filename then make sure it has been set properly.

To make sure you don't try to modify the input add the ACCESS=READONLY option to the LIBNAME statement.

So try this:

%put &=courseroot;
LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/poverty.xlsx" access=readonly;
proc contents data=CoImpt._all_;
run;

If you are using SAS On Demand then I assume you are using SAS/Studio.

So find the XLSX file you want to use in the Files and Folders view in SAS/Studio and get the PROPERTIES for that file.  Copy the full filename (or path name) for the file and use that in your code.

[Reeza]

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.

[BLT2023]

Yes, this is my original file. The goal of using this file within the project is to use PROC TRANSPOSE to take it from a wide dataset to a long one, remove the word 'FIPS' from the front of each of the observations containing a FIPS code, and convert that variable from character to numeric. I will eventually merge it with another dataset. 

[Reeza]

When I take your file as posted and upload to Studio and run the following code, this is what I get. 

Can you run the exact same code and show the output?

Also, verify the path to the file is correct by right clicking the file in Studio and selecting properties. Make sure that matches the path in your code.