Prev: zipcode distance caculation
Next: SAS in iPad?
From: Amar Mundankar on 28 Apr 2010 01:00
Hi all,
I have the following 2 datasets.

data one;
id = 1; var1 = 4; output;
id = 1; var1 = 1; output;
id = 1; var1 = 3; output;
id = 2; var1 = 14; output;
id = 2; var1 = 24; output;
id = 2; var1 = 38; output;
run;
data two;
name = 'a';output;
name = 'b';output;
name = 'c';output;
run;

Required Output:

Id Var1 name
1 4 a
1 1 b
1 3 c
2 14 a
2 24 b
2 38 c

How can I add the column name in the output ? There is no common
variable in both the tables.
In the output the number of records for every Id will be 3 only and
the records in the Two dataset are also 3.

What are the different ways to achieve the required output ??

Thanks in Advance.

Regards,
Amar Mundankar.
From: Matt C on 28 Apr 2010 08:40
On Apr 28, 1:00 am, Amar Mundankar <amarmundan...(a)gmail.com> wrote:
> Hi all,
> I have the following 2 datasets.
>
> data one;
>         id = 1; var1 = 4; output;
>         id = 1; var1 = 1; output;
>         id = 1; var1 = 3; output;
>         id = 2; var1 = 14; output;
>         id = 2; var1 = 24; output;
>         id = 2; var1 = 38; output;
> run;
> data two;
>          name = 'a';output;
>          name = 'b';output;
>          name = 'c';output;
> run;
>
> Required Output:
>
> Id Var1 name
> 1  4      a
> 1  1      b
> 1   3     c
> 2   14   a
> 2  24     b
> 2   38   c
>
> How can I add the column name in the output ? There is no common
> variable in both the tables.
> In the output the number of records for every Id will be 3 only and
> the records in the Two dataset are also 3.
>
> What are the different ways to achieve the required output ??
>
> Thanks in Advance.
>
> Regards,
> Amar Mundankar.

I think you might try stacking the data instead by using the 'SET'
command:

DATA NEWFILE;
SET old1 old2;
RUN;

Cheers,
M
From: data _null_; on 28 Apr 2010 08:47
On Apr 28, 12:00 am, Amar Mundankar <amarmundan...(a)gmail.com> wrote:
> Hi all,
> I have the following 2 datasets.
>
> data one;
>         id = 1; var1 = 4; output;
>         id = 1; var1 = 1; output;
>         id = 1; var1 = 3; output;
>         id = 2; var1 = 14; output;
>         id = 2; var1 = 24; output;
>         id = 2; var1 = 38; output;
> run;
> data two;
>          name = 'a';output;
>          name = 'b';output;
>          name = 'c';output;
> run;
>
> Required Output:
>
> Id Var1 name
> 1  4      a
> 1  1      b
> 1   3     c
> 2   14   a
> 2  24     b
> 2   38   c
>
> How can I add the column name in the output ? There is no common
> variable in both the tables.
> In the output the number of records for every Id will be 3 only and
> the records in the Two dataset are also 3.
>
> What are the different ways to achieve the required output ??
>
> Thanks in Advance.
>
> Regards,
> Amar Mundankar.

If you have less than three obs per ID in WORK.ONE this wont work
properly.

data three;
do point=1 to nobs;
set one;
set two point=point nobs=nobs;
output;
end;
run;
From: Amar Mundankar on 28 Apr 2010 10:52
On Apr 28, 5:47 pm, "data _null_;" <datan...(a)gmail.com> wrote:
> On Apr 28, 12:00 am, Amar Mundankar <amarmundan...(a)gmail.com> wrote:
>
>
>
>
>
> > Hi all,
> > I have the following 2 datasets.
>
> > data one;
> >         id = 1; var1 = 4; output;
> >         id = 1; var1 = 1; output;
> >         id = 1; var1 = 3; output;
> >         id = 2; var1 = 14; output;
> >         id = 2; var1 = 24; output;
> >         id = 2; var1 = 38; output;
> > run;
> > data two;
> >          name = 'a';output;
> >          name = 'b';output;
> >          name = 'c';output;
> > run;
>
> > Required Output:
>
> > Id Var1 name
> > 1  4      a
> > 1  1      b
> > 1   3     c
> > 2   14   a
> > 2  24     b
> > 2   38   c
>
> > How can I add the column name in the output ? There is no common
> > variable in both the tables.
> > In the output the number of records for every Id will be 3 only and
> > the records in the Two dataset are also 3.
>
> > What are the different ways to achieve the required output ??
>
> > Thanks in Advance.
>
> > Regards,
> > Amar Mundankar.
>
> If you have less than three obs per ID in WORK.ONE this wont work
> properly.
>
> data three;
>    do point=1 to nobs;
>       set one;
>       set two point=point nobs=nobs;
>       output;
>       end;
>    run;- Hide quoted text -
>
> - Show quoted text -

Hi ,
This is what I wanted.
Thanks you all for your solution.

Thanks and Regards,
Amar Mundankar.
From: Richard A. DeVenezia on 28 Apr 2010 11:14
On Apr 28, 1:00 am, Amar Mundankar <amarmundan...(a)gmail.com> wrote:
> Hi all,
> I have the following 2 datasets.
>
> data one;
>         id = 1; var1 = 4; output;
>         id = 1; var1 = 1; output;
>         id = 1; var1 = 3; output;
>         id = 2; var1 = 14; output;
>         id = 2; var1 = 24; output;
>         id = 2; var1 = 38; output;
> run;
> data two;
>          name = 'a';output;
>          name = 'b';output;
>          name = 'c';output;
> run;
>
> Required Output:
>
> Id Var1 name
> 1  4      a
> 1  1      b
> 1   3     c
> 2   14   a
> 2  24     b
> 2   38   c
>
> How can I add the column name in the output ? There is no common
> variable in both the tables.
> In the output the number of records for every Id will be 3 only and
> the records in the Two dataset are also 3.
>
> What are the different ways to achieve the required output ??

Amar:

One way is to use a DO loop to pointedly iterate over the second table
while the implicit DATA Step loop iterates over the first table.

This sample code uses a mod function just in case the first table has
more rows in a group than there are rows in the second table.

--------
data one;
input id var1 @@;
datalines;
1 4 1 1 1 3
2 14 2 24 2 38
run;

data two;
input name $ @@;
datalines;
a b c
run;

data want;
set one;
by id;

if first.id then
index = 1;
else
index = mod(index,maxindex)+1;

set two nobs=maxindex point=index;
run;
--------

A second way would involve creating synthetic keys for each table and
joining them with SQL


--
Richard A. DeVenezia
http://www.devenezia.com
 | 
Pages: 1
Prev: zipcode distance caculation
Next: SAS in iPad?