topic Re: Concatenate strings with missing values in SAS Programming

Concatenate strings with missing values

daisy6 — Fri, 06 Oct 2017 16:02:02 GMT

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.

Re: Concatenate strings with missing values

RW9 — Fri, 06 Oct 2017 16:19:16 GMT

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.

Re: Concatenate strings with missing values

daisy6 — Fri, 06 Oct 2017 16:18:34 GMT

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;

Re: Concatenate strings with missing values

RW9 — Fri, 06 Oct 2017 16:30:58 GMT

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;

Re: Concatenate strings with missing values

daisy6 — Fri, 06 Oct 2017 16:42:36 GMT

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

Re: Concatenate strings with missing values

daisy6 — Fri, 06 Oct 2017 16:44:26 GMT

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

Re: Concatenate strings with missing values

Tom — Fri, 06 Oct 2017 17:49:57 GMT

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;

Re: Concatenate strings with missing values

RW9 — Fri, 06 Oct 2017 19:24:49 GMT

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.

Re: Concatenate strings with missing values

daisy6 — Fri, 06 Oct 2017 20:16:59 GMT

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