Hello, my have and want tables are as following
data have; input id_appel id_demande $ demand_label $20. ; cards; 1 A-01 Appointement 1 C-09 Contract 2 A-08 Appointement 2 D-10 Devis 2 C-11 Contract 2 C-29 Contract ; run; data want; input id_appel nb_dmd_A nb_dmd_B nb_dmd_C nb_dmd_D ; cards ; 1 1 0 1 0 2 1 0 2 1 ; run;
This code is work for me
proc sort data= have; by id_appel demand_label; quit; data want; set have; by id_appel; if first.id_appel then do; nb_dmd_A=0; nb_dmd_B=0; nb_dmd_C=0; nb_dmd_D=0; end; retain nb_dmd_: 0; if demand_label="Appointement" then nb_dmd_A = nb_dmd_A+1; if demand_label="BBB" then nb_dmd_B = nb_dmd_B+ 1; if demand_label="Contract" then nb_dmd_C = nb_dmd_C+1; if demand_label="Devis" then nb_dmd_D= nb_dmd_D+ 1; if last.id_appel; drop id_demande demand_label; run;
I am looking to optimise my codes because i have many demand_label different .
Can my codes be optimized ? Can you help me please?
If you have any suggestion i am open for more details ...
Is there any reason you need to use DEMAND_LABEL in your program? Can't you just use the first character in ID_DEMANDE instead? This might get you most of the way there:
proc freq data=have; tables id_appel * id_demande / noprint out=counts (keep=id_appel id_demande count);
format id_demande $1.; run;
You still need to reformat the counts (including adding zeros where appropriate ... see if the SPARSE option would do that for you), but wouldn't those counts be correct?
Dynamic code is preferable IMO even if slightly longer.
I'll leave the renaming of columns up to you.
data have; input id_appel id_demande $ demand_label $20. ; cards; 1 A-01 Appointement 1 C-09 Contract 2 A-08 Appointement 2 D-10 Devis 2 C-11 Contract 2 C-29 Contract ; run; proc sort data= have; by id_appel id_demande; quit; proc format; value $ demande_list 'A' = 'A' 'B' = 'B' 'C' = 'C' 'D' = 'D'; run; proc tabulate data=have out=long; class id_appel; class id_demande / preloadfmt; format id_demande $demande_list1.; table id_appel, id_demande*N='' / printmiss misstext='0'; run; data long; set long; if missing(N) then N=0; run; proc transpose data=long out=want; by id_appel; id id_demande; var N; run;
Hope this will help you-
proc freq data=have; tables id_appel*demand_label / list nocol norow out=work.tmp(drop=percent) ; run; proc sort data=tmp1; by demand_label; run; data tmp1; length demand_label $1.; set tmp; run; proc transpose data=tmp1 out=tmp2; by id_appel; id demand_label; var count; run;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.