BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

I have some rows that are separated by commas, eg ROW1 :Wells (column1) / D2E,D3R,D5H,H6J (column2) and ROW2 : Dons (coulmn1) / H5N,J9T, F4S (columns=2) and I want each value in the commas to be separated as such :

So from this:

Wells   D2E,D3R,D5H,H6J

Dons    H5N,J9T,F4S

to this:

Wells D2E

Wells D3R

Wells D5H

Wells H6J

Dons H5N

Dons J9T

Dons F4S

Thank you

6 REPLIES 6
art297
Opal | Level 21

I think you are asking how to accomplish the following:

data have;

  informat name $10.;

  informat invalue $20.;

  input name invalue @;

  cards;

Wells   D2E,D3R,D5H,H6J

Dons    H5N,J9T,F4S

;

data want (keep=name value);

  set have;

  length value $3;

  i=1;

  do while (scan(invalue,i,",") ne "");

    value=scan(invalue,i,",");

    i+1;

    output;

  end;

run;

FriedEgg
SAS Employee

data foo;

infile cards dlm='|';

input (name data) (:$20.);

do i=1 to count(data,',')+1;

  column2=scan(data,i,',');

  output;

end;

keep name column2;

cards;

Wells|D2E,D3R,D5H,H6J

Dons|H5N,J9T,F4S

;

run;

podarum
Quartz | Level 8

Great help guys, thanks...Smiley Happy

podarum
Quartz | Level 8

Sorry guys, I have something I wasn't aware of (I forgot to express it), and don't know how to fix it:

Wells   D2E,D3R,D5H,H6J

Dons    H5N,J9T,F4S

Gens   L2G 7H9

Leads  K9P 5F9

to this:

Wells D2E

Wells D3R

Wells D5H

Wells H6J

Dons H5N

Dons J9T

Dons F4S

Gens L2G

Leads K9P

right now I get

Wells D2E

Wells D3R

Wells D5H

Wells H6J

Dons H5N

Dons J9T

Dons F4S

Gens L2G

Gens 7H9  (no need)

Leads K9P

Leads 5F9 (no need)

art297
Opal | Level 21

Then you didn't use the code I had proposed as, if your values are always three characters, it would have eliminated the unwanted entries.

FriedEgg
SAS Employee

I agree with Art, to get the result you say you now retrieve you would have to be using code other than that which we specified.

data foo;

infile cards dlm='|';

input (name data) (:$20.);

do i=1 to count(data,',')+1;

  column2=scan(data,i); *change made here to remove specified delimiter, it will still produce the same result but now properly scans instead of truncating to length;

  output;

end;

keep name column2;

cards;

Wells|D2E,D3R,D5H,H6J

Dons|H5N,J9T,F4S

Gens|L2G 7H9

Leads|K9P 5F9

;

run;

Wells D2E

Wells D3R

Wells D5H

Wells H6J

Dons H5N

Dons J9T

Dons F4S

Gens L2G

Leads K9P

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2237 views
  • 0 likes
  • 3 in conversation