## SAS code to create a new dataset

Solved
Occasional Contributor
Posts: 6

# SAS code to create a new dataset

Hello Everyone,

I have a dataset with firm identifier, a dummy variable DUMM, and ratio of males to female "Ratio". I want to create a new dataset where I want to select firms whose male to female ratio was greater than 0.5 when DUMM is 0 and less than 0.25 when DUMM is 1. Also I want to make sure that the firm has both 0 and 1 DUMM. My current data set looks as follows:

Company     DUMM     Ratio

A                    0          0.6

A                    1          0.2

B                    0          0.4

B                    1          0.15

C                    0          0.75

D                    0          0.8

D                    1          0.3

E                    0          0.55

E                    1          0.18

In the above case, the firms that satisfies all the conditions are firm A and E. I want my new dataset to look as follows:

Company         DUMM_0      Dumm_1     Ratio_Dumm0     Ratio_Dumm1

A                         0               1               0.6                         0.2

E                         0               1               0.55                        0.18

Can please someone tell me how I could get the desired output?

Thank you

Sam

Accepted Solutions
Solution
‎10-10-2012 02:29 PM
Super Contributor
Posts: 1,636

## Re: SAS code to create a new dataset

four steps:

data have;
input Company\$     DUMM     Ratio     EPS;
cards;
A                    0          0.6          1
A                    1          0.2          2
B                    0          0.4          0.5
B                    1          0.15        1.1
C                    0          0.75         6
D                    0          0.8          5
D                    1          0.3          3
E                    0          0.55          2.5
E                    1          0.18          1.3
;
proc transpose data=have out=want1(drop=_ prefix=ratio_dumm ;
by company;
var ratio;
id dumm;
run;
proc transpose data=have out=want2(drop=_ prefix=dumm_ ;
by company;
var dumm;
id dumm;
run;
proc transpose data=have out=want3(drop=_ prefix=EPS_dumm ;
by company;
var eps;
id dumm;
run;
data want;
merge  want2 want1 want3;
run;
proc print;run;
ratio_   ratio_    EPS_    EPS_
Obs   Company   dumm_0   dumm_1    dumm0    dumm1   dumm0   dumm1

1       A         0        1      0.60     0.20     1.0     2.0
2       B         0        1      0.40     0.15     0.5     1.1
3       C         0        .      0.75      .       6.0      .
4       D         0        1      0.80     0.30     5.0     3.0
5       E         0        1      0.55     0.18     2.5     1.3

All Replies
Super User
Posts: 5,878

## Re: SAS code to create a new dataset

Transpose the data using PROC TRANSPOSE, the result would be easier to apply your filter on.

Data never sleeps
Posts: 3,167

## Re: SAS code to create a new dataset

Update: no array() needed:

data have;

input Company\$     DUMM     Ratio;

cards;

A                    0          0.6

A                    1          0.2

B                    0          0.4

B                    1          0.15

C                    0          0.75

D                    0          0.8

D                    1          0.3

E                    0          0.55

E                    1          0.18

;

data want;

do until (last.company);

set have;

by company notsorted;

if first.company then i=0;

if dumm=0 and ratio>0.5 then do; i+1; ratio_dumm0=ratio; end;

else if dumm=1 and ratio<0.25 then do; i+1; ratio_dumm1=ratio;end;

end;

retain dumm0 0 dumm1 1;

if i=2 then output;

drop i dumm ratio;

run;

proc print;run;

I suppose using Proc transpose may lessen the some hard coding here.

Haikuo

Super User
Posts: 6,767

## Re: SAS code to create a new dataset

It's probably being too picky. but it would theoretically be better to ignore the variable i.  In theory, a company could have three records, all with DUMM=0 and RATIO > 0.5.  Instead of conditioning on i=2, select companies having ratio_dumm0 > . and ratio_dumm1 > .  (Even that could also be flawed if an original value of RATIO was missing, but that's another story.  There are ways to program around pretty much anything that might happen with the data.)  The best solution might depend on how much you have checked your data beforehand.

Super Contributor
Posts: 1,636

## Re: SAS code to create a new dataset

three steps:

data have;
input Company\$     DUMM     Ratio;
cards;
A                    0          0.6
A                    1          0.2
B                    0          0.4
B                    1          0.15
C                    0          0.75
D                    0          0.8
D                    1          0.3
E                    0          0.55
E                    1          0.18
;

proc transpose data=have out=want1(drop=_ prefix=ratio_dumm ;
by company;
var ratio;
id dumm;
run;
proc transpose data=have out=want2(drop=_ prefix=dumm_ ;
by company;
var dumm;
id dumm;
run;
data want;
merge  want2 want1;
run;
proc print;run;

Occasional Contributor
Posts: 6

## Re: SAS code to create a new dataset

Thank you guys for the codes. It does work. I have a small change in my data. In addition to the Firm identifier, DUMM, and RATIO, I also have an additional variable EPS. I want to get the new dataset with EPS_Dumm0 and EPS_Dumm1. So in other words, if I continue with the above example:

Company     DUMM     Ratio     EPS

A                    0          0.6          1

A                    1          0.2          2

B                    0          0.4          0.5

B                    1          0.15        1.1

C                    0          0.75         6

D                    0          0.8          5

D                    1          0.3          3

E                    0          0.55          2.5

E                    1          0.18          1.3

In the above case, the firms that satisfies all the conditions are firm A and E. I want my new dataset to look as follows:

Company         DUMM_0      Dumm_1     Ratio_Dumm0     Ratio_Dumm1     EPS_DUMM0     EPS_DUMM1

A                         0               1               0.6                         0.2                    1                         2

E                         0               1               0.55                        0.18                   2.5                    1.3

Thank you.

Sam

Solution
‎10-10-2012 02:29 PM
Super Contributor
Posts: 1,636

## Re: SAS code to create a new dataset

four steps:

data have;
input Company\$     DUMM     Ratio     EPS;
cards;
A                    0          0.6          1
A                    1          0.2          2
B                    0          0.4          0.5
B                    1          0.15        1.1
C                    0          0.75         6
D                    0          0.8          5
D                    1          0.3          3
E                    0          0.55          2.5
E                    1          0.18          1.3
;
proc transpose data=have out=want1(drop=_ prefix=ratio_dumm ;
by company;
var ratio;
id dumm;
run;
proc transpose data=have out=want2(drop=_ prefix=dumm_ ;
by company;
var dumm;
id dumm;
run;
proc transpose data=have out=want3(drop=_ prefix=EPS_dumm ;
by company;
var eps;
id dumm;
run;
data want;
merge  want2 want1 want3;
run;
proc print;run;
ratio_   ratio_    EPS_    EPS_
Obs   Company   dumm_0   dumm_1    dumm0    dumm1   dumm0   dumm1

1       A         0        1      0.60     0.20     1.0     2.0
2       B         0        1      0.40     0.15     0.5     1.1
3       C         0        .      0.75      .       6.0      .
4       D         0        1      0.80     0.30     5.0     3.0
5       E         0        1      0.55     0.18     2.5     1.3

Occasional Contributor
Posts: 6

## Re: SAS code to create a new dataset

Thank you for the prompt reply.

🔒 This topic is solved and locked.