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

Hello All,

I have a problem. I have to split the row based on a column value.

My data is like :

Key  Coupon   ID

1       1234      A

2        0100     B

3.       0081     C

Otput should be

Coupon      ID

1               A

2               A    

3               A    

4               A              

1               B

8               C

1               C

ie. if for all the non-zero values in Column Coupon, the entire row should repeat except Column Coupon. The Vaue in coupon should be splied in 1 char.

Please help me. I'm new for SAS programming.

1 ACCEPTED SOLUTION

Accepted Solutions
Resa
Pyrite | Level 9

Of course there are several ways to skin a cat, but this is what I can suggest.

The first part of the code is to "rebuilt" your input dataset in which I assumed that coupon is not necessarily limited to 4 characters (I set the length to 20)

The second part creates the output dataset. Basic principle is to go through the coupon content (renamed since the output has a different length then the original) character by character and write a record to the output dataset if the content is not equal to 0.

data have ;

     length key 8 coupon $20 ID $1 ;

     input key coupon $ ID $ ;

     datalines ;

1 1234 A

2 0100 B

3 0081 C

;

run ;

data want (keep=coupon ID) ;

     length coupon $1 ID $1 ;

     set have (rename=(coupon=old_coupon)) ;

     do i=1 to length(strip(old_coupon)) ;

          if substr(old_coupon,i,1)^='0' then do ;

               coupon = substr(old_coupon,i,1) ;

               output ;

          end ;

     end ;

run ;

I hope this helps.

View solution in original post

7 REPLIES 7
Resa
Pyrite | Level 9

Of course there are several ways to skin a cat, but this is what I can suggest.

The first part of the code is to "rebuilt" your input dataset in which I assumed that coupon is not necessarily limited to 4 characters (I set the length to 20)

The second part creates the output dataset. Basic principle is to go through the coupon content (renamed since the output has a different length then the original) character by character and write a record to the output dataset if the content is not equal to 0.

data have ;

     length key 8 coupon $20 ID $1 ;

     input key coupon $ ID $ ;

     datalines ;

1 1234 A

2 0100 B

3 0081 C

;

run ;

data want (keep=coupon ID) ;

     length coupon $1 ID $1 ;

     set have (rename=(coupon=old_coupon)) ;

     do i=1 to length(strip(old_coupon)) ;

          if substr(old_coupon,i,1)^='0' then do ;

               coupon = substr(old_coupon,i,1) ;

               output ;

          end ;

     end ;

run ;

I hope this helps.

Rohan
Calcite | Level 5

Thank you Resa,, u rocks.. Smiley Happy

Ksharp
Super User

How about:

data have ;
     length key 8 coupon $20 ID $1 ;
     input key coupon $ ID $ ;
     datalines ;
1 1234 A
2 0100 B
3 0081 C
;
run ;
data want(keep=_coupon id);
set have;
j=0; 
do until(j=0);
j=findc(coupon,'123456789',j+1);
if j ne 0 then do;_coupon=substr(coupon,j,1);output;end;
end;
run;


Ksharp

Rohan
Calcite | Level 5

Thank you for response.. Smiley Happy

Linlin
Lapis Lazuli | Level 10

data have;

input Key  Coupon $ ID $;

cards;

1      1234     A

2      0100     B

3      0081     C

;

data want(keep=_c id rename=(_c=coupon));

  length _c $ 1;

  set have;

  c=compress(coupon,'123456789','k');

  do i=1 to length(c);

  _c=substr(c,i,1);

  output;

  end;

run;

proc print;run;

Linlin

Rohan
Calcite | Level 5

Thank you for response..

Saugata
Obsidian | Level 7

I may be late in responding, but you can try the below code also:

data have ;

    length key 8 coupon $20 ID $1 ;

            input key coupon $ ID $ ;

    datalines ;

                        1 1234 A

                        2 0100 B

                        3 0081 C

;

run ;

data want (keep= coupon Id);  

            set have;

            coupon2=compress(compress(coupon,0));

            i=1;

            do while (substr(coupon2,i,1) ne '');

                        coupon=substr(coupon2,i,1);

                        output;

                        i+1;

            end;

run;

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!

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
  • 7 replies
  • 803 views
  • 3 likes
  • 5 in conversation