topic Re: Writing duplicate records based on key in SAS Programming

Writing duplicate records based on key

gandikk — Thu, 23 Aug 2018 15:23:22 GMT

Hi,

I have an input which has 3 fields.

10011438696014	222,160,539,533,265	Richard
10011438695678	246,187,739,563	Brian

I need to create an output based on the 2nd field like below. Comma is my delimiter in the 2nd field. I need to create duplicate records based on the comma separated values in the 2nd field.

10011438696014	222	Richard
10011438696014	160	Richard
10011438696014	539	Richard
10011438696014	533	Richard
10011438696014	265	Richard
10011438695678	246	Brian
10011438695678	187	Brian
10011438695678	739	Brian
10011438695678	563	Brian

How I can achieve this in SAS? Thanks in advance.

Re: Writing duplicate records based on key

novinosrin — Thu, 23 Aug 2018 15:31:00 GMT

Straight forward scan

data have;
input (field1-field3) (:$50.);
cards;
10011438696014	222,160,539,533,265	Richard
10011438695678	246,187,739,563	Brian
;

data want;
set have;
do _n_=1 to countw(field2,',');
_field2=scan(field2,_n_,',');
output;
end;
drop field2;
run;

Re: Writing duplicate records based on key

Reeza — Thu, 23 Aug 2018 15:33:21 GMT

COUNTW - how many words in the field

SCAN() to get ith element.

data want;
set have;

nwords  = countw(string);

do i=1 to nwords;
word =scan(string, i);
output;
end;

keep var1 word name;
run;

@gandikk wrote:

Hi,

I have an input which has 3 fields.

10011438696014 222,160,539,533,265 Richard

10011438695678 246,187,739,563 Brian

I need to create an output based on the 2nd field like below. Comma is my delimiter in the 2nd field. I need to create duplicate records based on the comma separated values in the 2nd field.

10011438696014 222 Richard

10011438696014 160 Richard

10011438696014 539 Richard

10011438696014 533 Richard

10011438696014 265 Richard

10011438695678 246 Brian

10011438695678 187 Brian

10011438695678 739 Brian

10011438695678 563 Brian

How I can achieve this in SAS? Thanks in advance.