BookmarkSubscribeRSS Feed
yuen68
Calcite | Level 5

Here is the raw dataset,

ID value

A 1

A 2

A 3

B 1

B 2

B 3

C 1

C 2

C 3

Expected output:

ID Var1 Var2 Var3

A     1     2     3

B     1     2     3

C     1     2     3

How could I make it by one data step? I have tried array but still cant figure out the correct assignment.... May there be any hints on inputting data like this?

Million thankss.......

9 REPLIES 9
Tom
Super User Tom
Super User

If you already have the data use PROC TRANSPOSE

proc transpose data=have prefix=var out=want ;

  by id ;

var value;

run;

yuen68
Calcite | Level 5

Thanks for your suggestion Tom.

May I use a single data step to do so except the proc transpose?

Tom
Super User Tom
Super User

There are two problems with trying to do this with a data step.

1) How large to make the array.  You could just set an upper limit to get the program to run.

2) How will the data step know when you have reached the end of the list of values for the current ID.

It might be easier to make a view to read the data and then call PROC TRANSPOSE or another data step to roll the data up.

data have / view=have ;

  infile sample ;

  input id $ value ;

run;


proc transpose data=have out=want prefix=var ;

  by id;

  var value ;

run;

or

data want ;

  do i=1 by 1 until (last.id);

    set have ;

    by id;

    array var (4);  * What value to use for size of the array??? ;

    var(i)=value ;

  end;

  drop i value ;

run;

You could do it in a single data step, but you will need to have the data in separate file so that you can tell when you have reached the end to enable outputting the last observation.  (note there is a trick with parmcards that can get around this if you must).

data want;

  infile sample end=eof truncover ;

  input @1 next $ @@;

  id = next ;

  array var (4) ;

  do i=1 by 1 until (next ne id ) ;

    input value ;

    var(i) = value;

    if not eof then input next $ @@ ;

    else next = ' ';

  end;

  output;

  drop next value i ;

run;

yuen68
Calcite | Level 5

Hi Tom,

what if it's given that there are three fixed ID (i.e. A,B,C) but unknown numbers of var? Will it be possible to use the array + do loop to complete it?

Ksharp
Super User

You can refer to my and Arthur.T's paper at SGF , which demonstrate how to use data step instead of proc transpose :

http://support.sas.com/resources/papers/proceedings13/517-2013.pdf
http://support.sas.com/resources/papers/proceedings13/538-2013.pdf




 

 
data have;
input ID $ value ;
cards;
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
;
run;
proc sql noprint;
 select max(value) into : n from have;
quit;
data want(keep=id var:);
 set have;
 by id;
 array v{*} var1-var%left(&n);
 retain var:;
 v{value}=value;
 if last.id then do; output; call missing(of v{*});end;
run;
 


Ksharp

Message was edited by: xia keshan

Patrick
Opal | Level 21

Hi Ksharp

I was looking forward to reading this paper which Art once mentioned in a post. Thanks for posting the link.

Cheers

Patrick

Ksharp
Super User

You are welcome. Actually , those papers were written almost by Arthur.T . I even have no time to review these papers and give some good advices.

Sorry , Arthur.T   !

Ksharp

Message was edited by: xia keshan

LillianLee
Calcite | Level 5

Data work;

         input ID $ Var1 @

               / @3 Var2 @

               / @3 Var3;

Cards;

A 1

A 2

A 3

B 1

B 2

B 3

C 1

C 2

C 3

;

run;









NagendraKumarK
Calcite | Level 5


data hi;
input id$  val1 /
   id$ val2 /
   id$   val3;

datalines;
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
;
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
  • 9 replies
  • 1156 views
  • 0 likes
  • 6 in conversation