BookmarkSubscribeRSS Feed
turcay
Lapis Lazuli | Level 10

Hello everyone,

 

My purpose is to divide the table 3 equal groups depending on one variable. On the other hand, I would like to see my Target variable 1 and 0 values. Additively, I also want to see ranges(Min and Max) for every groups.  My desired output as below;

 

Desired.png

 

I prepared a sample data, first I used PROC RANK procedure and then I used PROC MEANS I can get Min and Max ranges and Total Count but I cannot get count values for 1 and 0 for my Target variable.

 

Data Have;
Length Score 8 Target 8;
Infile Datalines missover;
Input Score Target;
Datalines;
1200 1
1210 0
1220 1
1230 0
1240 1
1250 0
1260 1
1270 0
1280 1
1290 0
1300 0
1310 1
1320 0
1330 0
1340 1
1350 0
1360 0
1370 1
1380 0
1390 0
1400 1
1410 0
1420 0
1430 0
1440 1
1450 0
1460 0
1470 0
1480 1
1490 0
1500 0
;
Run;
PROC RANK DATA=Have Groups=3 Out=Ranked_Have;
Var Score;
Ranks Bucket;
Run;

PROC MEANS DATA=Ranked_Have NWAY NOPRINT;
CLASS BUCKET;
VAR SCORE;
OUTPUT OUT=RANGES MIN=MIN MAX=MAX;
RUN;

Then I tried PROC TABULATE procudure and some addition steps to reach my aim. However, this time, I could not get ranges(Max and Min).

 

PROC TABULATE DATA=Ranked_Have Out=DefaultCounts(DROP=_TYPE_ _PAGE_ _TABLE_);
	CLASS Target / Order=Unformatted Missing;
	CLASS Bucket / Order=Unformatted Missing;
	TABLE Bucket,
		Target*N ALL={LABEL="Total (ALL)"}*N;
RUN;

PROC SORT DATA=DefaultCounts(KEEP=N BUCKET) OUT=SORTED_COUNT;
BY BUCKET;
RUN;

PROC TRANSPOSE DATA=SORTED_COUNT OUT=SORTED_COUNT2(DROP=_NAME_ RENAME=(TARGET1=NonDefault TARGET2=Default_Count TARGET3=Total_Count))
PREFIX=TARGET;
BY BUCKET;
VAR N;
RUN;

Can anybody help me about this situation, please?

 

Thank you,

3 REPLIES 3
Reeza
Super User

but I cannot get count values for 1 and 0 for my Target variable

 

Number of 1s is the SUM of the TARGET variable.

Number of 0s is the N-SUM of the TARGET variable.

 

Add those additional statistics to your PROC MEANS and then in a data step do the subtraction to the get the number of 0's. 

Or if you want to add a different step you could try PROC FREQ. 

turcay
Lapis Lazuli | Level 10

Thank you,

 

I tried something but it does not work. Could you tell where should I put N, NMISS and SUM function in the following procedure?

 

PROC MEANS DATA=Ranked_Have NOPRINT MIN MAX N NMISS SUM	; 
CLASS BUCKET;
VAR SCORE;
VAR Target;
OUTPUT OUT=RANGES SUM=Target NMISS=Target MIN=MIN MAX=MAX ;
RUN;
Reeza
Super User
PROC MEANS DATA=Ranked_Have NWAY  N NMISS MEAN MIN MAX STACKODS;
CLASS BUCKET;
VAR SCORE TARGET ;
ods output summary=ranked_stats;
RUN;

@turcay wrote:

Thank you,

 

I tried something but it does not work. Could you tell where should I put N, NMISS and SUM function in the following procedure?

 

PROC MEANS DATA=Ranked_Have NOPRINT MIN MAX N NMISS SUM	; 
CLASS BUCKET;
VAR SCORE;
VAR Target;
OUTPUT OUT=RANGES SUM=Target NMISS=Target MIN=MIN MAX=MAX ;
RUN;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2066 views
  • 0 likes
  • 2 in conversation