BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
prajakta
Fluorite | Level 6

I need to create a new record if multiple values are available in id column.

Input:

 

idnamecity
1abcmumbai
2def/jklpune
3mnopDelhi

 

Required Output:

 

idnamecity
1abcmumbai
2defpune
2jklpune
3mnopDelhi

 

Thanks..!!

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Amethyst | Level 16

Hi @prajakta ,

 

try this:

data have;
  input id	name $	city $;
  cards;
1	abc	Mumbai
2	def/jkl	Pune
3	mnop	Delhi
;
run;

data want (rename=(n=name));
  set have;
  n = name;
  _N_ = countw(n, "/");
  do _N_ = 1 to _N_;
    name = scan(n, _N_, "/");
    output;
  end;
  drop n;
run;
proc print;
run;

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

3 REPLIES 3
andreas_lds
Jade | Level 19

You need:

  • if + countw to check if there are multiple values in id
  • a loop, with scan to extract the values
  • the output-statement create the observations

 

If you want code, post data in usable form.

yabwon
Amethyst | Level 16

Hi @prajakta ,

 

try this:

data have;
  input id	name $	city $;
  cards;
1	abc	Mumbai
2	def/jkl	Pune
3	mnop	Delhi
;
run;

data want (rename=(n=name));
  set have;
  n = name;
  _N_ = countw(n, "/");
  do _N_ = 1 to _N_;
    name = scan(n, _N_, "/");
    output;
  end;
  drop n;
run;
proc print;
run;

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



prajakta
Fluorite | Level 6

Thanks..!! it works for me...

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1020 views
  • 0 likes
  • 3 in conversation