Concatenate strings with missing values

[daisy6]

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.

[RW9]

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.

[daisy6]

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;

[RW9]

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;

[daisy6]

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

[daisy6]

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

[RW9]

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.

[Tom]

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;

[daisy6 · Accepted solution]

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