topic Re: Concatenating string variables based on IF-THEN in SAS Programming

Concatenating string variables based on IF-THEN

Wolverine — Wed, 28 Jun 2017 13:38:50 GMT

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;

Re: Concatenating string variables based on IF-THEN

art297 — Wed, 28 Jun 2017 13:58:27 GMT

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

Re: Concatenating string variables based on IF-THEN

Kurt_Bremser — Wed, 28 Jun 2017 13:58:28 GMT

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-data-AKA-generate/ta-p/258712 to convert the imported SAS dataset into a datastep for posting here.

Re: Concatenating string variables based on IF-THEN

Astounding — Wed, 28 Jun 2017 14:03:20 GMT

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;

Re: Concatenating string variables based on IF-THEN

Wolverine — Wed, 28 Jun 2017 15:00:02 GMT

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).

Re: Concatenating string variables based on IF-THEN

art297 — Wed, 28 Jun 2017 16:16:01 GMT

@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

Re: Concatenating string variables based on IF-THEN

Wolverine — Wed, 28 Jun 2017 17:46:29 GMT

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!