DATA Step, Macro, Functions and more

Concatenating string variables based on IF-THEN

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Concatenating string variables based on IF-THEN

[ Edited ]

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;

 

 


Accepted Solutions
Solution
‎06-28-2017 01:36 PM
PROC Star
Posts: 7,474

Re: Concatenating string variables based on IF-THEN

[ Edited ]
Posted in reply to Wolverine

@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


All Replies
PROC Star
Posts: 7,474

Re: Concatenating string variables based on IF-THEN

Posted in reply to Wolverine

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

Super User
Posts: 7,809

Re: Concatenating string variables based on IF-THEN

Posted in reply to Wolverine

Please post example data. Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert the imported SAS dataset into a datastep for posting here.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,511

Re: Concatenating string variables based on IF-THEN

Posted in reply to Wolverine

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;

Contributor
Posts: 42

Re: Concatenating string variables based on IF-THEN

Posted in reply to Astounding
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).

Solution
‎06-28-2017 01:36 PM
PROC Star
Posts: 7,474

Re: Concatenating string variables based on IF-THEN

[ Edited ]
Posted in reply to Wolverine

@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

Contributor
Posts: 42

Re: Concatenating string variables based on IF-THEN

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 120 views
  • 0 likes
  • 4 in conversation