SAS code to create a new dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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=_Smiley Happy prefix=ratio_dumm ;
by company;
var ratio;
id dumm;
run;
proc transpose data=have out=want2(drop=_Smiley Happy prefix=dumm_ ;
by company;
var dumm;
id dumm;
run;
proc transpose data=have out=want3(drop=_Smiley Happy 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

View solution in original post


All Replies
Super User
Posts: 5,437

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
Respected Advisor
Posts: 3,156

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: 5,516

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=_Smiley Happy prefix=ratio_dumm ;
by company;
var ratio;
id dumm;
run;
proc transpose data=have out=want2(drop=_Smiley Happy 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=_Smiley Happy prefix=ratio_dumm ;
by company;
var ratio;
id dumm;
run;
proc transpose data=have out=want2(drop=_Smiley Happy prefix=dumm_ ;
by company;
var dumm;
id dumm;
run;
proc transpose data=have out=want3(drop=_Smiley Happy 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. Smiley Happy

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 308 views
  • 8 likes
  • 5 in conversation