Hi there, I am trying to create a data using data step, based on PROC MEANS OUTPUT (means_all table) AND REGRESS OUTPUT (estimates table). The thing is that I have multiple values as I used the CLASS statement in both of them. The next picture shows three categories, AG, MAN and SR and below the means for the variables R and L. I need to take all the means for each class so, together with the estimates I can compute some operations. I have done this before but just having one class using first obs and keep, but having Classes it did not work. This is my shot.
DATA WANT;
SET ESTIMATES;
IF _N_ = 1 THEN DO;
SET MEANS_ALL (FIRSTOBS = 2 KEEP = R_MEAN L_MEAN);
RENAME R_MEAN = R_MEAN_AG;
RENAME LNTA_MEAN = L_MEAN_AG;
SET MEANS_ALL (FIRSTOBS = 3 KEEP = R_MEAN L_MEAN);
RENAME ROA_MEAN = R_MEAN_MAN;
RENAME LNTA_MEAN = L_MEAN_MAN ;
SET MEANS_ALL (FIRSTOBS = 5 KEEP = R_MEAN L_MEAN);
RENAME ROA_MEAN = R_MEAN_SR;
RENAME LNTA_MEAN = L_MEAN_SR ;
END;
RUN;
Couldn't you transpose the means output and then join with the estimates on response and variable?
Something like
proc transpose
data=means
out=means_trans (
rename=(_name_=variable col1=mean)
)
;
by response;
var _numeric_;
run;
proc sql;
create table want as
select a.*, b.mean
from estimates a left join means_trans b
on a.variable = b.variable and a.response = b.response;
quit;
Can you show us what the desired output would look like?
Show test data before means output, in the form of a datastep, and what the output should look like. Its seems a bit of a faff and you may be able to simplfy the whole thing by normalising before means processing.
Oh, and please stop shouting code at us.
data WORK.DATA;
infile datalines dsd truncover;
input ID:BEST. SEC:$3. R:COMMA15.4 L:BEST. E:COMMA15.4;
format ID BEST. R COMMA15.4 L BEST. E COMMA15.4;
label ID="ID" SEC="SEC" R="R" L="L" E="E";
datalines;
1115 OTH 0.0032 10.684656256 0.0095
1494 OTH 0.0025 12.517256378 0.0000
1498 SER 0.0026 10.138639711 0.0327
1501 MAN 0.0028 9.564539273 0.0001
1515 MAN 0.0047 11.239027947 0.0967
;;;;
/* step 1. Estimates */
proc logistic data = data plots = none outest = mlogit_estimates;
model sec (ref = 'oth')= r l e / link = glogit ; run;
/* step 2. Means */
proc means data = data mean maxdec = 4 ; class sec ; output out = means_all mean = average / autoname ; run;
/* step 3 create a data set where both tables are combined */
data partial;
set
mlogit_estimates (drop = _link_ _type_ _status_ _name_ _lnlike_ _esttype_)
means_all ;
if (sec = 'agr') then
rename r_mean = r_mean_agr
l_mean = l_mean_agr
e_mean_agr;
if (sec = 'man') then
rename r_mean = r_mean_man
l_mean = l_mean_man
e_mean_man;
if (sec = 'ser') then
rename r_mean = r_mean_ser
l_mean = l_mean_ser
e_mean_ser;
step_1_agr = exp (r_estimate_agr * r_mean_agr * l_mean_agr);
step_2_man = exp (l_estimate_man * l_mean_man);
step_3_ser = exp (0);
prob_agr = (step_1_agr / (1 + step_1_agr + step_2_man + step_3_ser));
prob_man = (step_2_man / (1 + step_1_agr + step_2_man + step_3_ser));
prob_ser = (step_3_ser / (1 + step_1_agr + step_2_man + step_3_ser));
par_der_agr = e_estimate_agr(prob_agr)( 1 - prob_agr);
par_der_man = e_estimate_man(prob_man)( 1 - prob_man);
par_der_ser = eq_estimate_ser(prob_ser)( 1 - prob_ser);
run;
proc print data = partial label noobs; var par_der_agr ; format _all_ comma15.4 ; run;
proc print data = partial label noobs; var par_der_man ; format _all_ comma15.4 ; run;
proc print data = partial label noobs; var par_der_ser ; format _all_ comma15.4 ; run;
Sorry for the late response. So many events yesterday. I am trying to increase my SAS skills but I am still in baby steps. I have put the code and the formulas or steps I need to do to get my results. The only thing I am still struggling is the fact that I can get me variables in the same table and use them on my steps.
My data partial does not work as I am trying to rename the variables by sector so I can use them in the step below.
@Kurt_Bremser the transpose means it indeed works but no the SQL. Still, the data in means_trans is not handy as I need (that's my perception maybe it is). By the way, very good article, Maxims of Maximally... I am printing it to make my bible.
@PaigeMiller I am looking values such as, (of course including the whole data)
P(AGR) | 0.175088 |
P(MAN) | 0.237825 |
P(SER) | 0.293544 |
@RW9 I have put now the code now so ya can see what I am trying to do. On the other hand, I did not mean to shout code to you all, I just like the way how my SAS code looks in capital letters. In future post, I will be aware of it.
Thank you all
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.