How to repeat all observation individually based on category field?

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

How to repeat all observation individually based on category field?

Hi,

I have 2 data set

First data set contain

id date coeff1 coeff2 coeff3 coeff4

101 2/3/2013 0 1 0 1

102 3/4/2013 1 0 1 0

104 3/5/2013 1 1 0 0

105 4/5/2013 1 1 1 0

109 5/8/2013 1 1 1 0

Second data set contain:

Category

A

B

C

D


What i want is:

category id date coeff1 coeff2 coeff3 coeff4

A 101 2/3/2013 0 1 0 1

A 102 3/4/2013 1 0 1 0

A 104 3/5/2013 1 1 0 0

A 105 4/5/2013 1 1 1 0

A 109 5/8/2013 1 1 1 0

B 101 2/3/2013 0 1 0 1

B 102 3/4/2013 1 0 1 0

B 104 3/5/2013 1 1 0 0

B 105 4/5/2013 1 1 1 0

B 109 5/8/2013 1 1 1 0

...

..

..

D 101 2/3/2013 0 1 0 1

D 102 3/4/2013 1 0 1 0

D 104 3/5/2013 1 1 0 0

D 105 4/5/2013 1 1 1 0

D 109 5/8/2013 1 1 1 0

How to do this using sas?

Thanks,

Ganesh K



Accepted Solutions
Solution
‎06-19-2015 09:42 AM
Super User
Posts: 9,878

Re: How to repeat all observation individually based on category field?

CODE NOT TESTED.

proc sql;

create table want as

select *

  from table1 , table2 ;

quit;

Xia Keshan

View solution in original post


All Replies
Solution
‎06-19-2015 09:42 AM
Super User
Posts: 9,878

Re: How to repeat all observation individually based on category field?

CODE NOT TESTED.

proc sql;

create table want as

select *

  from table1 , table2 ;

quit;

Xia Keshan

Frequent Contributor
Posts: 92

Re: How to repeat all observation individually based on category field?

Read the second dataset in and create an array CATEGORY. creating dataset 3 with 1 observation and the Category array with all the values of Category.  Then read dataset 1 and dataset 3.  output each record in dataset 1, once for each element in the Category array. Then sort the data by Category. 

data one;

input id date coeff1 coeff2 coeff3 coeff4;

informat date mmddyy10.;

p=1;

cards;

101 2/3/2013 0 1 0 1

102 3/4/2013 1 0 1 0

104 3/5/2013 1 1 0 0

105 4/5/2013 1 1 1 0

109 5/8/2013 1 1 1 0

proc print;  run;

data two;

input categ $2.;

p=1;

array cata $2  cata1-cata26;   retain cata1-cata26;

n+1;  cata{n}=categ;  drop categ ;

if categ='d' then output;

cards;

a

b

c

d

proc  print;  run;

data three;   merge one two;  by p;

array cata $2  cata1-cata26; retain cata1-cata26 n;

format date mmddyy10.;

do i=1 to n;  category=cata{i};  output;  end;

  drop cata1-cata26 p n i;

proc sort;  by category id;

proc print; id category;  run;

Jim

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 217 views
  • 3 likes
  • 3 in conversation