DATA Step, Macro, Functions and more

Input with unique ID

Reply
Occasional Contributor
Posts: 6

Input with unique ID

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.......

Super User
Super User
Posts: 7,074

Re: Input with unique ID

If you already have the data use PROC TRANSPOSE

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

  by id ;

var value;

run;

Occasional Contributor
Posts: 6

Re: Input with unique ID

Thanks for your suggestion Tom.

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

Super User
Super User
Posts: 7,074

Re: Input with unique ID

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;

Occasional Contributor
Posts: 6

Re: Input with unique ID

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?

Super User
Posts: 10,044

Re: Input with unique ID

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

Respected Advisor
Posts: 4,173

Re: Input with unique ID

Hi Ksharp

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

Cheers

Patrick

Super User
Posts: 10,044

Re: Input with unique ID

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

Occasional Contributor
Posts: 10

Re: Input with unique ID

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;









Occasional Contributor
Posts: 14

Re: Input with unique ID


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;


Ask a Question
Discussion stats
  • 9 replies
  • 294 views
  • 0 likes
  • 6 in conversation