Survey Data Analysis

[Pooja_Chhetri]

Hello everyone,

I am doing my survey data analysis and I encountered one problem. I am not able to increase the cell width in SAS due to which SAS is not able to display all the information that I have in the excel file. Actually I was trying to convert Likert scale data into numeric value. As the SAS cell width is small, it can only displayed "somewhat" instead of "somewhat important" and "somewhat unimportant". Due to which I was not able to change it to numeric values. Can anyone please provide me the SAS code which help me to change cell width? 

Thanks.

[SASJedi]

Please share the code you're using so we can help assess the problem.

[Pooja_Chhetri]

This is two of the SAS CODE which I have used.

proc report data=survey;
columns Q16_1 Q16_2 Q16_3 Q16_4 Q16_5 Q16_6 Q16_7;
define Q16_1 / style(column)=[cellwidth=50in];
define Q16_2 / style(column)=[cellwidth=20in];
define Q16_3 / style(column)=[cellwidth=20in];
define Q16_4 / style(column)=[cellwidth=20in];
define Q16_5 / style(column)=[cellwidth=20in];
define Q16_6 / style(column)=[cellwidth=20in];
define Q16_7/ style(column)=[cellwidth=20in];
title "Using the CELLWIDTH= Style with PROC REPORT";
run;

proc report data = survey;
title 'Responses';
column Name Q16_1 Q16_2 Q16_3 Q16_4 Q16_5 Q16_6 Q16_7 ;
define Q16_1 / width = 20;
define Q16_2 /width = 5 ;
define Q16_3 /width = 5 ;
define Q16_4 /width = 5 ;
define Q16_5 / width = 5;
define Q16_6 / width = 5;
define Q16_7/ width = 5;
run;
proc print data=survey;
run;

[SASJedi]

I used this test code which deliberately sets the width too small for the data. I ran the code in PC SAS (9.4 M8), Enterprise Guide 8.3, and SAS Viya 2022.1 LTS:

title 'Test';
title2 "Using the CELLWIDTH= Style with PROC REPORT";
proc report data = sashelp.cars(obs=10);
columns Make Model Type;
   define Make / style(column)=[cellwidth=1in];
   define Model / style(column)=[cellwidth=1in];
   define Type / style(column)=[cellwidth=1in];
run;

title 'Test';
title2 "Using the WIDTH= option with PROC REPORT";
proc report data = sashelp.cars(obs=10) ;
	columns Make Model Type;
	define Make / width =1;
	define Model /width =1;
	define Type /width =1;
run;
title;

They all produced the same results, and none of the data was truncated:

Test

Using the CELLWIDTH= Style with PROC REPORT

Make	Model	Type
Acura	MDX	SUV
Acura	RSX Type S 2dr	Sedan
Acura	TSX 4dr	Sedan
Acura	TL 4dr	Sedan
Acura	3.5 RL 4dr	Sedan
Acura	3.5 RL w/Navigation 4dr	Sedan
Acura	NSX coupe 2dr manual S	Sports
Audi	A4 1.8T 4dr	Sedan
Audi	A41.8T convertible 2dr	Sedan
Audi	A4 3.0 4dr	Sedan

---

Test

Using the WIDTH= option with PROC REPORT

Make	Model	Type
Acura	MDX	SUV
Acura	RSX Type S 2dr	Sedan
Acura	TSX 4dr	Sedan
Acura	TL 4dr	Sedan
Acura	3.5 RL 4dr	Sedan
Acura	3.5 RL w/Navigation 4dr	Sedan
Acura	NSX coupe 2dr manual S	Sports
Audi	A4 1.8T 4dr	Sedan
Audi	A41.8T convertible 2dr	Sedan
Audi	A4 3.0 4dr	Sedan

Do you get these same results using the test code? If so, what's different with your original output?

[Pooja_Chhetri]

Thank you for the reply.  Now I am able to increase the width of cell but the actual problem is: In my excel file, there are options like very important, somewhat important, neither, somewhat unimportant and very unimportant but when I print this data in SAS, it is only displaying very imp instead of very important, and somewhat for both somewhat important and somewhat unimportant. Due to which I was not able to convert the characters into numeric values. How can this issue be fixed?

[ballardw]

First we need a workable description of your data. Listing a few values doesn't do it as how you read the data into SAS is critical.

So first thing: Run Proc contents on your data set and share the result. If you aren't familiar with that procedure the basic results are extremely easy:

Proc contents data=yourdatasetname;
run;

The data set name will require the library as well if not in the work library.

This will tell is the defined characteristics of the variables. It is not impossible that value was truncated when the data was read into SAS. So if we see Length such as 8 for the variable it can only hold 8 characters and indicates the data was read incorrectly to hold a value like "somewhat important". Second the details will show us the assigned format. We have people complain about the values being "incorrect" when it turns out that they somehow assigned a display format of $5 which means that only 5 characters are shown by default in output even if the variable contains 25 or 5,000.

If you read data from a text file you can use a custom Informat to read the values directly into a numeric value and use a custom format to display the text value when needed.  If you search this format you will find that issues related to "reading" or "importing" data from Excel is about the single most frequent topic because there are so many ways that people can make spreadsheets and most of them are not intended for data interchange and require anywhere from a little to a great deal of intervention to make SAS data sets. Spreadsheets coupled with Proc Import are a common cause of short assigned variable lengths. Proc Import by default only uses 20 rows of data to guess the properties of a column to make into a SAS variable. So if the first 20 rows of data only have a value like "agree" then the length would be set to 5 characters so all the "somewhat agree" encountered later in the the file are truncated to "somew". There a many variations on this. A column that should be numeric but has some values recorded as "Missing" "NA" "< 5" may end up as character when those not-actually-a-number value are encountered.

Additionally, if you have two or more spreadsheet files what are supposed to contain similar data, when using Proc Import to read the data each file is treated separately and can result in variables having different lengths, different types and even different names depending on the files.

[Pooja_Chhetri]

This is the result I get by running proc contents. Please let me know if you need any more information. I used this code to import the excel file in SAS. 

data survey; **select a name;
infile 'C:\Users\chhetrip\Downloads\Logging\Responses.csv' DLM=',' DSD MISSOVER firstobs=2; *tell where to find data;
input Q2 $ Q3_1_1 $ Q3_2_1 $ Q3_3_1 Q4 Q5 Q6 $ Q7_1_1 Q7_2_1 Q7_3_1 Q7_4_TEXT $ Q7_4_1 Q8 $ Q9 Q10_1_1 Q10_2_1 Q10_3_1 Q10_4_1 Q11_1_1 Q11_2_1 Q11_3_1 Q11_4_1 Q12_1_1 Q12_2_1 Q12_3_1 Q12_4_1 Q12_5_TEXT $ Q12_5_1 Q13_1 Q13_2 Q14_1_1 Q14_1_2 Q14_2_1 Q14_2_2 Q14_3_1 Q14_3_2 Q14_4_1 Q14_4_2 Q14_5_1 Q14_5_2 Q14_6_1 Q14_6_2 Q14_7_TEXT $ Q14_7_1 Q14_7_2 Q15_1 Q15_2 Q15_3 Q15_4 Q15_4_TEXT $ Q16_1 $ Q16_2 $ Q16_3 $ Q16_4 $ Q16_5 $ Q16_6 $ Q16_7 $ Q16_7_TEXT $ Q17_1 Q17_2 Q18_1 Q18_2 Q18_3 Q18_4 Q18_5 Q19 $ Q20 $ Q21 $ Q22 Q23 $ Q24 $ Q25 $ Q26 $;
run;
proc contents data= survey;
run;

[ballardw]

Congratulations on attempting to read the values with a data step.

However you allowed the default $ input to be used with all of your character variables. So none of them are reading more than 8 characters.

Without having your data I would change all of those of $ to :$20. if you want to read up to 20 characters. If another length makes sense use that instead of 20

Or a more sophisticated example using a custom informat and matching format:

Proc format;
invalue lickert (upcase)
'DISAGREE'                 =1
'SOMEWHAT DISAGREE'        =2
'NEITHER AGREE OR DISAGREE'=3
'SOMEWHAT AGREE'           =4
'AGREE'                    =5
;
value lickert
1='Disagree'                 
2='Somewhat Disagree'        
3='Neither Agree or Disagree'
4='Somewhat Agree'           
5='Agree'    
;
run;

data example;
   infile datalines dlm=',';
   input respondent Q :Lickert.;
datalines;
1,disagree
2,Disagree
3,Agree
4,Agree
5,Somewhat Disagree
6,somewhat agree
7,neither agree or disagree
;

Proc freq data=example;
   tables q;
   format q lickert.;
run;

The Invalue in Proc format is to read text into a standard value, in this case a 5 point numeric scale. Note that the (UPCASE) option means compare an upper case version of the text to list before assigning the numeric value. This can help if people are entering data and aren't good about the case of spelling. Or if you have different pieces of software collecting data on what collected the values as "Agree", another as 'AGREE' and another as 'agree'. Actually it will handle stuff like 'aGrEE' as well.

Then there is a corresponding display format to show 'nice' text when needed for the raw scale values. This approach of a numeric value with a format means that you can display values in numeric order and not fight with the order of character values where the order would tend to be 'Agree' 'Disagree' 'Neither' 'Somewhat agree' 'Somewhat disagree'. So desired order can be used.

If you  have different text but want to map to same numbers you can overload the list such as combining "Disagree" "Worst" "Lowest" by separating the values with a comma, or separate assignments in the Invalue block. You would want to have different display formats though.

Names of the Informat or Format created cannot end in a digit as ending digits are used for lengths. So if you want to have a different Informat or Format for Q2 you would want to use a name like Q2_ on the Invalue or Value statement.

Suggestion: In the step that you read your data add a block of LABEL assignments with the text, or a reasonable paraphrase, of the question involved. Then the data set will sort of document itself plus many procedures will use the label by default if there is one assigned.

[Pooja_Chhetri]

When I change $ to $ 20, you can see in the 1st picture what I get and that was not what I was looking for. As for the suggestion, I have rename all the variable after importing the data. Then when I start changing the Likert scale data into numeric value that's when the problem starts.  After some trial, I then realized it is because SAS is not displaying every information (characters) excel contains (figure 2) .

As I am new to SAS programming, I did not understand how to apply that sophisticated code to my data analysis to get the desired results. Hope to get more helps in this issue.

Thank you.

[ballardw · Accepted solution]

Show the LOG with the code when you read the data. Copy the code and all the notes or messages from the LOG. On the forum open a text box using the </> icon that appears above the main message window. The paste all the text from the log.

That is : $20. no space after the $ and end with a period to indicate that is an informat. The colon before the informat is related to where SAS starts reading the next value. When you place formats on the INPUT statement you can end up with SAS reading starting at the incorrect place and for the incorrect number of columns of text. The : modifies the input to help with some of those issues.

OR separate the Informat information from the INPUT statement. Place this before the INPUT and use your previous Input statement.

Informat Q2 Q6 Q8 Q13 Q20 Q21 <list all the CHAR variables> $20.;

[Pooja_Chhetri]

Yes, it worked. Thank you so much.