DATA Step, Macro, Functions and more

Concatenate strings with missing values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

Concatenate strings with missing values

Hello,

I have some data :

 

rid   form   questions  response

1      F          qt1            A

1      F          qt2            B

1      F          qt3            C

...... 

1      F           qt50          D

......

24      F            qt1          B

24     F             qt2         

24    F            qt3             C

.....

 

I want my output like

1      ABC.......D

.....

24   B C.........

 

if candidates did not answer, the output need the space for that question.

Any reply is appreciated.

 


Accepted Solutions
Solution
‎10-12-2017 10:13 AM
Frequent Contributor
Posts: 102

Re: Concatenate strings with missing values

Thank you very much! Tom, I solve that problem with discussion with my colleague. I appreciate your time and help. 

View solution in original post


All Replies
Super User
Super User
Posts: 9,402

Re: Concatenate strings with missing values

[ Edited ]

Assumes sorted:

Edit, changed as I don' think original will work: - As noted this is where test data so I can run things comes in useful!

data want;
  set have;
  by rid;
length qs $50; if first.rid then qs="";
substr(qs,input(tranwrd(questions,"qt",""),best.),1)=strip(resp);
if last.rid then output; run;

 

Old:

data want;
  set have;
  by rid;
  resp=ifc(first.rid,response,cat(resp,ifc(resp=""," ",resp)));
  if last.rid then output;
run;

It a good idea to post test data in the form of a datastep in the text of the post, as such the above is not tested.

Frequent Contributor
Posts: 102

Re: Concatenate strings with missing values

data test;
input id$10. form$3. question$10. response$3.;
datalines;
123456789 F question1 A
123456789 F question2 B
123456789 F question3 C
123456789 F question4 D
234567891 F question1 A
234567891 F question2 A
234567891 F question3 D
234567891 F question4 D
345678912 F question1 A
345678912 F question2
345678912 F question3 C
345678912 F question4 D
;
run;

Super User
Super User
Posts: 9,402

Re: Concatenate strings with missing values

Thanks, this works:

data test;
  infile datalines missover;
  length id form question response $10;
  input id $ form $ question $ response $;
datalines;
123456789 F question1 A
123456789 F question2 B
123456789 F question3 C
123456789 F question4 D
234567891 F question1 A
234567891 F question2 A
234567891 F question3 D
234567891 F question4 D
345678912 F question1 A
345678912 F question2
345678912 F question3 C
345678912 F question4 D
;
run;
data want;
  set test;
  by id;
  length qs $50;
  retain qs;
  if first.id then qs="";
  substr(qs,input(tranwrd(question,"question",""),best.),1)=strip(response);
  if last.id then output;
run;
Frequent Contributor
Posts: 102

Re: Concatenate strings with missing values

Thanks for help, the code works for my test code. 

Frequent Contributor
Posts: 102

Re: Concatenate strings with missing values

However the third id didn't answer question2 and the output of qs need the blank at the second location.

Super User
Super User
Posts: 9,402

Re: Concatenate strings with missing values

Sorry, what is wrong with my code then.  When I run it the one record with a missing character is a space in the output - exactly what you asked for??  Basically it checks each question number against the character position in the output string, so missing will be a missing character in the string.  Please provide examples - test / output - if something has changed.

Super User
Super User
Posts: 7,932

Re: Concatenate strings with missing values

You could use SUBSTR() function as the TARGET of an assignment statement to place the response in the proper place.

This code works for you example. It assumes that all groups have all questions and they are in the right order. 

data test;
  length id $10 form $3 question $10 response $3;
  input id--response ;
datalines;
123456789 F question1 A
123456789 F question2 B
123456789 F question3 C
123456789 F question4 D
234567891 F question1 A
234567891 F question2 A
234567891 F question3 D
234567891 F question4 D
345678912 F question1 A
345678912 F question2 .
345678912 F question3 C
345678912 F question4 D
;

data want ;
  do q=1 by 1 until (last.form);
    set test ;
    by id form ;
    length want $10 ;
    substr(want,q,1)=response  ;
  end;
  drop q question response;
run;
Obs       id        form    want

 1     123456789     F      ABCD
 2     234567891     F      AADD
 3     345678912     F      A CD

If your data is not sorted by QUESTION or some rows are missing then instead of generating Q from the record order you could derive it from the value of the QUESTION variable.

data want ;
  do until (last.form);
    set test ;
    by id form ;
    length want $10 ;
    q=input(compress(question,' ','dk'),32.);
    substr(want,q,1)=response  ;
  end;
  drop q question response;
run;
Solution
‎10-12-2017 10:13 AM
Frequent Contributor
Posts: 102

Re: Concatenate strings with missing values

Thank you very much! Tom, I solve that problem with discussion with my colleague. I appreciate your time and help. 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 232 views
  • 0 likes
  • 3 in conversation