BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Wolverine
Quartz | Level 8

I have a somewhat messy Excel file (which I've imported into SAS) that was used to recode open-ended survey responses into a set of categories.  However, there is a "68-Other" code, and when that code is present, I need to keep the open-ended response.  Otherwise, the open-ended response should be deleted.

 

Making things a bit more complicated is that both the recodes and the open-ended responses are spread across several variables.  The end file should have 3 recode variables and 1 open-end variable.

 

So, in English, the code is supposed to do this: Look at all 3 of the recode variables.  If none of them are 68, then make the open-ended variables blank.  If any of the recode variables are 68, the open-ended variables should not be changed.  Then take the 3 open-ended variables and concatenate them into a single open-ended variable (named O_Q1AR1_recode_all_text).

 

In reality, the code doesn't make the open-ended variables blank even when 68 is not present.  And O_Q1AR1_recode_all_text always has the value ";;" whether the open-ended variables are blank or not.

 

/*THIS ISN'T WORKING FOR SOME REASON*/
/*If none of the codes = 68-Other, then the text variables should be blank*/
IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) 
	THEN O_Q1AR1_M1 = "" ;
	
IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) 
	THEN O_Q1AR1_M2 = "" ;
	
IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) 
	THEN O_Q1AR1_M3 = "" ;	


/*THIS ISN'T WORKING FOR SOME REASON*/
/*Concatenate the text variables*/
O_Q1AR1_recode_all_text = O_Q1AR1_M1||";"||O_Q1AR1_M2||";"||O_Q1AR1_M3;

RUN;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

@Wolverine: In your initial datastep you referenced a library that you never declared AND didn't include in your proc import.

 

I ran your code after removing the libname and it appeared to run as expected. Here is the modified datastep I used:

DATA /*DISNtemp.*/DISN_open_end_recode1TEST;
  KEEP resRespondent
       O_Q1AR1M1
       O_Q1AR1M1_Notes
       O_Q1AR1M2
       O_Q1AR1M2_Notes
       O_Q1AR1M3
       O_Q1AR1M3_Notes;
  SET /*DISNtemp.*/DISN_open_end_imported;
RUN;

 

Also, while you set the three notes' variable to missing if you find a 68, you never set them to the values you want to keep. e.g., you use:

  IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) 
	THEN O_Q1AR1_M1 = "" ;

but you never set O_Q1AR1_M1 to the value that you want. I think you meant to use something like:

  IF not(O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) THEN do;
    O_Q1AR1_M1 = O_Q1AR1M1;
    O_Q1AR1_M2 = O_Q1AR1M2;
    O_Q1AR1_M3 = O_Q1AR1M3;
  end;

 

Art, CEO, AnalystFinder.com

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

You didn't provide a subset of your data as an example, so I had to roll my own. Using that data, your code seemed to work fine:

data have;
  input O_Q1AR1_recode1-O_Q1AR1_recode3 (O_Q1AR1_M1-O_Q1AR1_M3) ($);
  cards;
2 68 3 x y z
4 3 2 a b c
55 21 68 d e f
;

/*THIS ISN'T WORKING FOR SOME REASON*/
/*If none of the codes = 68-Other, then the text variables should be blank*/
data want;
set have;
IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) 
	THEN O_Q1AR1_M1 = "" ;
	
IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) 
	THEN O_Q1AR1_M2 = "" ;
	
IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) 
	THEN O_Q1AR1_M3 = "" ;	


/*THIS ISN'T WORKING FOR SOME REASON*/
/*Concatenate the text variables*/
O_Q1AR1_recode_all_text = O_Q1AR1_M1||";"||O_Q1AR1_M2||";"||O_Q1AR1_M3;

RUN;

Art, CEO, AnalystFinder.com

Astounding
PROC Star

Variables have a fixed length in SAS.  At a minimum, consider adding the TRIM function to eliminate excessive blanks:

 

O_Q1AR1_recode_all_text = trim(O_Q1AR1_M1)||";"||trim(O_Q1AR1_M2)||";"||O_Q1AR1_M3;

Wolverine
Quartz | Level 8
PROC IMPORT datafile= "YOUR PATH\DISN_Completes_Open_End_TEST.xlsx"
    out=DISN_open_end_imported
    dbms=xlsx REPLACE;
sheet="DISN Completes Notes";
getnames=yes;


DATA DISNtemp.DISN_open_end_recode1TEST;
KEEP resRespondent O_Q1AR1M1 O_Q1AR1M1_Notes O_Q1AR1M2 O_Q1AR1M2_Notes O_Q1AR1M3
    O_Q1AR1M3_Notes;
SET DISNtemp.DISN_open_end_imported;

RUN;

DATA DISN_open_end_recode2TEST; SET DISN_open_end_recode1TEST; /*Concatenate the "Notes" variables*/ O_Q1AR1_recode_all = O_Q1AR1M1_Notes||O_Q1AR1M2_Notes||O_Q1AR1M3_Notes; /*Remove blanks, commas, parentheses, and perods*/ O_Q1AR1_recode_compr = compress(O_Q1AR1_recode_all, ' (),.'); /*Separate the concatenated codes into separate variables*/ O_Q1AR1_recode1 = SUBSTR(O_Q1AR1_recode_compr,1,2); O_Q1AR1_recode2 = SUBSTR(O_Q1AR1_recode_compr,3,2); O_Q1AR1_recode3 = SUBSTR(O_Q1AR1_recode_compr,5,2); /*THIS ISN'T WORKING FOR SOME REASON*/ /*If none of the codes = 68-Other, then the text variables should be blank*/ IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) THEN O_Q1AR1_M1 = "" ; IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) THEN O_Q1AR1_M2 = "" ; IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) THEN O_Q1AR1_M3 = "" ; /*THIS ISN'T WORKING FOR SOME REASON*/ /*Concatenate the text variables*/ O_Q1AR1_recode_all_text = TRIM(O_Q1AR1_M1)||";"||TRIM(O_Q1AR1_M2)||";"||TRIM(O_Q1AR1_M3); RUN;

title1 "Q1A concatenation";
PROC FREQ;
    TABLES O_Q1AR1_recode_all O_Q1AR1_recode_compr O_Q1AR1_recode1 O_Q1AR1_recode2 O_Q1AR1_recode3 O_Q1AR1_recode_all_text
        O_Q1AR1_M1;

RUN;

I attached a small portion of the data file, along with a larger section of my code (the first part of the code appears to be working fine).

art297
Opal | Level 21

@Wolverine: In your initial datastep you referenced a library that you never declared AND didn't include in your proc import.

 

I ran your code after removing the libname and it appeared to run as expected. Here is the modified datastep I used:

DATA /*DISNtemp.*/DISN_open_end_recode1TEST;
  KEEP resRespondent
       O_Q1AR1M1
       O_Q1AR1M1_Notes
       O_Q1AR1M2
       O_Q1AR1M2_Notes
       O_Q1AR1M3
       O_Q1AR1M3_Notes;
  SET /*DISNtemp.*/DISN_open_end_imported;
RUN;

 

Also, while you set the three notes' variable to missing if you find a 68, you never set them to the values you want to keep. e.g., you use:

  IF (O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) 
	THEN O_Q1AR1_M1 = "" ;

but you never set O_Q1AR1_M1 to the value that you want. I think you meant to use something like:

  IF not(O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) THEN do;
    O_Q1AR1_M1 = O_Q1AR1M1;
    O_Q1AR1_M2 = O_Q1AR1M2;
    O_Q1AR1_M3 = O_Q1AR1M3;
  end;

 

Art, CEO, AnalystFinder.com

Wolverine
Quartz | Level 8

Sorry, I forgot to remove the libname from the data step.

 

Anyway, this revised code fixed the issue:

 

IF not(O_Q1AR1_recode1 ^=68 AND O_Q1AR1_recode2 ^=68 AND O_Q1AR1_recode3 ^=68) THEN do;
    O_Q1AR1_M1 = O_Q1AR1M1;
    O_Q1AR1_M2 = O_Q1AR1M2;
    O_Q1AR1_M3 = O_Q1AR1M3;
  end;

 I assumed that if the IF-THEN statement didn't assign a new value, then the variables would retain their original value.  But I see from your revised code that isn't the case!

 

Thanks!Smiley Very Happy

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2449 views
  • 1 like
  • 4 in conversation