Rearrange the columns with data name

[SASdevAnneMarie]

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 !

[SASJedi]

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;

[SASdevAnneMarie]

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.

[SASJedi]

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

[Tom]

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

[SASdevAnneMarie]

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

[Tom · Accepted solution]

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')

[SASdevAnneMarie]

Thank you, Tom,

Where I must write thise line please : where S_TYPE_SUPPORT in ('UC','TXGAR') ? Befor the group by ?

[Tom]

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.  

[SASdevAnneMarie]

Thank you, Tom
I'm learning the sql. 🙂