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

Hi All,

 

could you please help me. I have one Variable with two delimiters ( : and , )

 

Variable
test1:aaaa,test2:bbbb,test3:ccc
test2:ddd,test1:ee25,test3:ffffff
test3:gg,test1:h,test2:jj12

 

 

I want to extract the Variables test1 to test3 with the Values.

test1test2test3
aaaabbbbccc
ee25dddffffff
hjj12gg

 

Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Looks like you have name/value pairs.  So generate a data structure that reflects that.

data tall ;
  set have ;
  do index=1 by 1 until(index>= countw(variable,','));
    length name $32 value $200 ;
    value = scan(variable,index,'.');
    name=scan(value,1,':');
    value=scan(value,2,':');
    output;
  end;
run;

If you want to generate that flattened file then using PROC TRANSPOSE.  Let's assume you also have a variable named ID that uniquely identifies the rows of the original dataset.

proc transpose data=tall out=want(drop=_name_);
  by id ;
  var value;
  id name;
run;

 

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

The SCAN function allows you to separate "words" using multiple delimiters.

 

Are the words TEST1 TEST2 TEST3 always present in every row, or could there be other words like TEST4 or GORILLA?

--
Paige Miller
Beecelal
Fluorite | Level 6

no some times in the variable just test1 and test 3 like that.

 

Variable 

......

test1;ll,test3:p12

 

There is no words "test2"

 

but I want to use always the words test1, test2, and test3 the new Variable names

Tom
Super User Tom
Super User

Looks like you have name/value pairs.  So generate a data structure that reflects that.

data tall ;
  set have ;
  do index=1 by 1 until(index>= countw(variable,','));
    length name $32 value $200 ;
    value = scan(variable,index,'.');
    name=scan(value,1,':');
    value=scan(value,2,':');
    output;
  end;
run;

If you want to generate that flattened file then using PROC TRANSPOSE.  Let's assume you also have a variable named ID that uniquely identifies the rows of the original dataset.

proc transpose data=tall out=want(drop=_name_);
  by id ;
  var value;
  id name;
run;

 

FreelanceReinh
Jade | Level 19

Hi @Beecelal,

 

You may want to try the "_infile_ trick":

data have;
input variable $50.;
cards;
test1:aaaa,test2:bbbb,test3:ccc
test2:ddd,test1:ee25,test3:ffffff
test3:gg,test1:h,test2:jj12
test1:ll,test3:p12
;

data want(keep=test:);
set have;
infile sasautos(verify.sas);
if _n_=1 then input @;
_infile_=translate(variable,'=',':,');
input @1 (test1-test3)(= :$10.) @@; /* increase length 10 if insufficient */
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

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
  • 4 replies
  • 690 views
  • 3 likes
  • 4 in conversation