Hello Experts,
I’m runing this code :
proc sql;
create table test2 as select distinct a.NO_POLICE, sum(MT_EA) as Encours,
a.S_TYPE_SUPPORT, a.D_VALO from test as a
group by a.NO_POLICE, a.S_TYPE_SUPPORT, a.D_VALO;
run;
to get the data like this:
POLICE |
Encours |
S_TYPE_SUPPORT |
D_VALO |
X070704733 |
5501.856756 |
UC |
09MAR2022:00:00:00 |
X070709982 |
13748.71306 |
UC |
09MAR2022:00:00:00 |
X070715392 |
73757.139242 |
TXGAR |
09MAR2022:00:00:00 |
X070715392 |
12415.318992 |
UC |
09MAR2022:00:00:00 |
X080175233 |
500040.2903 |
TXGAR |
09MAR2022:00:00:00 |
But I would like to represent my result in this way :
POLICE |
UC |
TXGAR |
D_VALO |
X070704733 |
5501.856756 |
09MAR2022:00:00:00 |
|
X070709982 |
13748.71306 |
09MAR2022:00:00:00 |
|
X070715392 |
12415.318992 |
73757.139242 |
09MAR2022:00:00:00 |
X080175233 |
500040.2903 |
09MAR2022:00:00:00 |
Do you know please, the best way to do that ?
Thank you for your help !
You should check whether PROC SUMMARY would do a better job of the summarization than PROC SQL. That is what it is designed to do.
If you do want to use PROC SQL then do not include the variable name as one of the columns.
select police
, d_valo
, sum(case when (S_TYPE_SUPPORT='UC') then MT_EA else . end) as UC
, sum(case when (S_TYPE_SUPPORT='TXGAR') then MT_EA else . end) as TXGAR
from have
group by police, d_valo
where S_TYPE_SUPPORT in ('UC','TXGAR')
This will get you close. If you really want missing values instead of zeros in the empty columns, you'll need to use a CASE expression
proc sql;
create table test2 as
select distinct a.NO_POLICE
,(S_TYPE_SUPPORT='UC') * sum(MT_EA) as UC
,(S_TYPE_SUPPORT='TXGAR') * sum(MT_EA) as TXGAR
, a.D_VALO
from test as a
group by a.NO_POLICE
,a.S_TYPE_SUPPORT
,a.D_VALO
;
quit;
Thank you , Jedi SAS.
I have this results.
Do you know please, how to obtain, for example for
X070715392 |
the result in one line :
I nested sum(S_TYPE_SUPPORT='UC') * sum(MT_EA) as sum( sum(S_TYPE_SUPPORT='UC') * sum(MT_EA) ) but it doesn't work.
Just don't group by S_TYPE_SUPPORT:
proc sql;
create table test2 as
select a.NO_POLICE "POLICE"
,sum((S_TYPE_SUPPORT='UC') * MT_EA) as UC
,sum((S_TYPE_SUPPORT='TXGAR') * MT_EA) as TXGAR
,a.D_VALO
from test as a
group by a.NO_POLICE
,a.D_VALO
;
quit;
Output:
POLICE | UC | TXGAR | D_VALO |
---|---|---|---|
X070704733 | 5501.857 | 0 | 09MAR2022:00:00:00 |
X070709982 | 13748.71 | 0 | 09MAR2022:00:00:00 |
X070715392 | 12415.32 | 73757.14 | 09MAR2022:00:00:00 |
X080175233 | 0 | 500040.3 | 09MAR2022:00:00:00 |
Using values of variables as names of new variables is what PROC TRANSPOSE does.
So if you have this data:
data have;
length POLICE $10 D_VALO 8 S_TYPE_SUPPORT $32 Encours 8;
input POLICE Encours S_TYPE_SUPPORT D_VALO :datetime.;
format d_valo datetime19.;
cards;
X070704733 5501.856756 UC 09MAR2022:00:00:00
X070709982 13748.71306 UC 09MAR2022:00:00:00
X070715392 73757.139242 TXGAR 09MAR2022:00:00:00
X070715392 12415.318992 UC 09MAR2022:00:00:00
X080175233 500040.2903 TXGAR 09MAR2022:00:00:00
;
You can run this step
proc transpose data=have out=want(drop=_name_);
by police d_valo;
id s_type_support;
var encours;
run;
To get this output
Obs POLICE D_VALO UC TXGAR 1 X070704733 09MAR2022:00:00:00 5501.86 . 2 X070709982 09MAR2022:00:00:00 13748.71 . 3 X070715392 09MAR2022:00:00:00 12415.32 73757.14 4 X080175233 09MAR2022:00:00:00 . 500040.29
Thank you, Tom.
I think that proc sql is better for the huge data ?
In my proc sql I want to add the sum to have the data in one line : like 0+121415 but I don't know how to do it :
My code is :
k.S_TYPE_SUPPORT, datepart(k.D_VALO) as D_VALO, (S_TYPE_SUPPORT='UC') * sum(MT_EA) as UC
,(S_TYPE_SUPPORT='TXGAR') * sum(MT_EA) as TXGAR,(S_TYPE_SUPPORT='DEDIE') * sum(MT_EA) as DEDIE
You should check whether PROC SUMMARY would do a better job of the summarization than PROC SQL. That is what it is designed to do.
If you do want to use PROC SQL then do not include the variable name as one of the columns.
select police
, d_valo
, sum(case when (S_TYPE_SUPPORT='UC') then MT_EA else . end) as UC
, sum(case when (S_TYPE_SUPPORT='TXGAR') then MT_EA else . end) as TXGAR
from have
group by police, d_valo
where S_TYPE_SUPPORT in ('UC','TXGAR')
Yes. It should be before the GROUP BY .
The main reason a lot of users are writing code using PROC SQL instead just normal SAS data steps and procedures is because they already know SQL syntax and it is easier for them. If you do not know SQL syntax then that is yet another reason to just use normal SAS process of PROC SUMMARY and PROC TRANSPOSE.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.