How to help SAS see column aligned data as more than one observation

[BLT2023]

When I run the code below, proc contents displays one observation (I am looking for 64). The way I know how to do this is to use a line pointer, but my code does not display each of the 64 variables for me to do so. How do I help SAS see that there is more than one observation in this code? 

Spoiler

LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Insurance.xlsx";
LIBNAME Check CLEAR;

LIBNAME	CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Insurance.xlsx";
PROC PRINT
		DATA = CoImpt.Insurance
		LABEL;
	
DATA want;
	SET CoImpt.Insurance;
	RUN;
	PROC CONTENTS DATA=want;
	RUN;

[ballardw]

WHAT does the LOG show when you run that code? Copy the text from the log including the code and all the notes, messages, warnings and/or errors, paste the text into a code box on the forum.

[BLT2023]

Here is the code from the log:

Spoiler

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Insurance.xlsx";
 NOTE: Libref CHECK was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Insurance.xlsx
 70         LIBNAME Check CLEAR;
 NOTE: Libref CHECK has been deassigned.
 71         
 72         LIBNAMECoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/Insurance.xlsx";
 NOTE: Libref COIMPT was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/Insurance.xlsx
 73         PROC PRINT
 74         DATA = CoImpt.Insurance
 75         LABEL;
 76         
 
 NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT.
 NOTE: The import data set has 1 observations and 64 variables.
 NOTE: The import data set has 1 observations and 64 variables.
 NOTE: There were 1 observations read from the data set COIMPT.Insurance.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           0.06 seconds
       user cpu time       0.06 seconds
       system cpu time     0.00 seconds
       memory              6263.18k
       OS Memory           64228.00k
       Timestamp           09/27/2023 08:27:28 PM
       Step Count                        746  Switch Count  0
       Page Faults                       0
       Page Reclaims                     1336
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            24
       Block Output Operations           8
       
 
 77         DATA want;
 78         SET CoImpt.Insurance;
 79         RUN;
 
 NOTE: The import data set has 1 observations and 64 variables.
 NOTE: There were 1 observations read from the data set COIMPT.Insurance.
 NOTE: The data set WORK.WANT has 1 observations and 64 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.04 seconds
       user cpu time       0.04 seconds
       system cpu time     0.00 seconds
       memory              5604.34k
       OS Memory           64488.00k
       Timestamp           09/27/2023 08:27:28 PM
       Step Count                        747  Switch Count  2
       Page Faults                       0
       Page Reclaims                     1047
       Page Swaps                        0
       Voluntary Context Switches        15
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 80         
 80       !  PROC CONTENTS DATA=want;
 81         RUN;
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           0.06 seconds
       user cpu time       0.06 seconds
       system cpu time     0.00 seconds
       memory              1381.34k
       OS Memory           60824.00k
       Timestamp           09/27/2023 08:27:28 PM
       Step Count                        748  Switch Count  0
       Page Faults                       0
       Page Reclaims                     104
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           40
       
 
 82         
 83         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 93         

[ballardw]

Look at your spreadsheet. I bet the "64 observations" you want occur on one row as separate columns in the spreadsheet.

That is from

 NOTE: The import data set has 1 observations and 64 variables.
 NOTE: There were 1 observations read from the data set COIMPT.Insurance.
 NOTE: The data set WORK.WANT has 1 observations and 64 variables.

64 variables means 64 columns with values in the original spreadsheet.

Good news, depending on the values, is that you may be able to transpose the data to 64 observations. This is an example of doing so with a data set with one row and 5 variables:

data example;
   input a b c var4 xyz;
datalines;
1 2 3 4 5
;

proc transpose data=example
      out=want (drop=_name_);
   var a b c var4 xyz;
run;

Proc transpose is designed to swap row/columns. It can make a data set long, such as above, or take a long set and make it "wide" though there are a number of restrictions.

The main restriction in your case is that ALL of the variables must be of the same type, numeric or character. Reason: each variable in SAS is either character or numeric. If you try to take values that are mixed and place them into the same variable (column) then SAS will not allow that.  If that is the case in your data then you may be able to do some work around but it likely will not end up with only one column.

[BLT2023]

Thank you for your response, but it did not seem to work for my dataset.

[ballardw]

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

[BLT2023]

There are no errors, but the data is reading in as one observation (since it is on one row). I am looking for it to read in 64 observations (the number of items in that row). Here is the log:

Spoiler

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         LIBNAME Check XLSX "&CourseRoot/CDPHE Study/Data/1_Source/county_alcohol.xlsx";
 NOTE: Libref CHECK was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/county_alcohol.xlsx
 70         LIBNAME Check CLEAR;
 NOTE: Libref CHECK has been deassigned.
 71         
 72         LIBNAME CoImpt XLSX "&CourseRoot/CDPHE Study/Data/1_Source/county_alcohol.xlsx";
 NOTE: Libref COIMPT was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /home/u63571562/BIOS6680_2023/CDPHE Study/Data/1_Source/county_alcohol.xlsx
 73         PROC PRINT
 74         DATA = CoImpt.County_Alcohol;
 75         LABEL;
 
 NOTE: Access by observation number not available. Observation numbers will be counted by PROC PRINT.
 NOTE: The import data set has 1 observations and 64 variables.
 NOTE: The import data set has 1 observations and 64 variables.
 NOTE: There were 1 observations read from the data set COIMPT.County_Alcohol.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           0.04 seconds
       user cpu time       0.04 seconds
       system cpu time     0.00 seconds
       memory              4812.81k
       OS Memory           25076.00k
       Timestamp           09/28/2023 02:36:13 PM
       Step Count                        37  Switch Count  0
       Page Faults                       0
       Page Reclaims                     1123
       Page Swaps                        0
       Voluntary Context Switches        11
       Involuntary Context Switches      1
       Block Input Operations            24
       Block Output Operations           24
       
 
 76         DATA want;
 77         SET CoImpt.County_Alcohol;
 78         RUN;
 
 NOTE: The import data set has 1 observations and 64 variables.
 NOTE: There were 1 observations read from the data set COIMPT.County_Alcohol.
 NOTE: The data set WORK.WANT has 1 observations and 64 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.01 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              4303.53k
       OS Memory           25336.00k
       Timestamp           09/28/2023 02:36:13 PM
       Step Count                        38  Switch Count  2
       Page Faults                       0
       Page Reclaims                     751
       Page Swaps                        0
       Voluntary Context Switches        16
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 79         
 79       !  PROC CONTENTS DATA=want;
 80         RUN;
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           0.07 seconds
       user cpu time       0.07 seconds
       system cpu time     0.00 seconds
       memory              1232.37k
       OS Memory           23208.00k
       Timestamp           09/28/2023 02:36:13 PM
       Step Count                        39  Switch Count  0
       Page Faults                       0
       Page Reclaims                     125
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           40
       
 
 81         
 82         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 92   

[ballardw]

I showed how to tranpose the data. I do not see any attempt to use that transpose method.

Your existing data is not in the form you want. I get it. Happens all the time. It is now up to you to make it they way that you want.

If you are going to rely on Proc Import you will learn to fix data because many many people, especially those creating data in spreadsheets, make lousy data transfers for real use.

The Proc Transpose I suggest would be for use on the Want data set created here:

76         DATA want;
 77         SET CoImpt.County_Alcohol;
 78         RUN;

The other option is to save that sheet from the XLSX file as a CSV (file Save As menu commands in your spreadsheet software. The write a data step that reads all the values into one variable and separate observations.

data example;
   infile datalines dlm=',';
   input value @@;
datalines;
1,2,3,4,5
;

Is an example of reading inline data. If you modify the INFILE to point the a CSV (NOT XLSX) file and remove the datalines section it will attempt to read all of the values into one variable at separate observations.

If your values are not numeric then you need to provide some instruction how to read them. An INFORMAT statement for the variable provides those rules. This would read the values as character with a length of up to 15 characters.

If your values are character and may include a comma as part of the value then add the DSD (delimiter sensitive data) to the Infile statement. If the values are Dates then an appropriate informat is needed and without seeing values can't suggest which to use. If you have numeric values with currency, percentage or imbedded commas then use a COMMA informat.

data example;
   infile datalines dlm=',';
   informat value $15.;
   input value @@;
datalines;
1,2,3,4,5
;

[Tom]

The data IS only one observations.

As explained above to convert the variables into multiple observations you can use PROC TRANPOSE.

* Create a SAS dataset from the EXCEL worksheet ;
data wide;
  set  coImpt.County_Alcohol;
run;

* Flip the dataset converting the rows into columns and columns into rows ;
proc transpose data=wide out=tall;
  var _all_;
run;

If the original dataset had 64 variables and 1 observation the new dataset will 64 observations and 2 variables.  There will be a variable called _NAME_ with the name of the original variable and one named COL1 with the value of that variable.

Note if ANY of the variables are character then COL1 will be character with any numeric variable's values converted to a character string. 

And if the original dataset had more observations the values from those observations will appear in variables COL2, COL3 etc.