BookmarkSubscribeRSS Feed
SASdevAnneMarie
Barite | Level 11

Hello Experts,

 

I would like to transpose my data and have the name of my variable in colum like this : 

My data :

SASdevAnneMarie_0-1704378003757.png

My data wanted :

 

SASdevAnneMarie_1-1704378047301.png

The idea of my code is : 

proc sql noprint;
select max(count) into :ngroups
from (select NO,count(*) as count from FINALE2 group by NO)
;
quit;


proc summary data=FINALE2 ;
  by NO;
  output out=want idgroup(out[&ngroups] (jour_mois CODE)=);
run;

But I don't have the right result, I think I need to use the proc transpose.

Could you help me please ?

I joinde the data.

3 REPLIES 3
PaigeMiller
Diamond | Level 26

Do yourself a favor and do not transpose the data set. You will find that the long data set is much easier to work with in SAS than this very wide data set that you want. Please explain what analyses you plan to do with this data.

 

If you need this wide layout for a report, there is no need to transpose the data set, you can do this with PROC REPORT and a long data set. Are you trying to create a report?

--
Paige Miller
ballardw
Super User

Suggestion: it appears that you have a date-like value but it is character.  Really for a vast majority of activities you want dates, times or datetime values stored as SAS numeric values with an appropriate format.

 

Your requested data layout appears to want variables with / in the variable name. That requires 1) setting system options VALIDVARNAME=any and then for every single such variable you would need to reference the names as name literals typing names with quotes and n for each. Example "FR10_31/1"n. Every. Single. Time. You will get tired typing those quotes and n very quickly, especially with the number of variables that transposition would create. Also those variable names wouldn't sort nicely just as your Jour_mois doesn't.

 

Can provide an example of what you will do that actually requires that structure?

Tom
Super User Tom
Super User

Please share data as text. (why go to the trouble to type it into Excel and then upload a file when you can just post the SAS code???)

data have;
  input NO $ MT_EA jour_mois $ IS_PROTOCOLE $ LP_ETAT_DOSS $ CD_RGA $;
cards;
A020 31413.16204 31/1 1112 VALID FR10
A020 31289.79011 28/2 1112 VALID FR10
A020 31396.92356 31/3 1112 VALID FR10
A020 31406.90669 30/4 1112 VALID FR10
A020 31413.5621 31/5 1112 VALID FR10
A020 31304.2403 30/6 1112 VALID FR10
A020 31390.58766 31/7 1112 VALID FR10
A020 31480.25608 31/8 1112 VALID FR10
A020 31516.72788 30/9 1112 VALID FR10
A020 32580.65359 31/10 1112 VALID FR10
A020 32620.42652 30/11 1112 VALID FR10
A020 33130.84571 31/12 1112 VALID FR10
A020 5000 31/1 1112 VALID FR20
A020 6000 28/2 1112 VALID FR20
A020 6000 31/3 1112 VALID FR20
A020 6000 30/4 1112 VALID FR20
A020 6000 31/5 1112 VALID FR20
A020 6000 30/6 1112 VALID FR20
A020 6000 31/7 1112 VALID FR20
A020 6000 31/8 1112 VALID FR20
A020 6000 30/9 1112 VALID FR20
A020 6000 31/10 1112 VALID FR20
A020 6000 30/11 1112 VALID FR20
A020 6000 31/12 1112 VALID FR20
;

From your output it looks like you just want to transpose ONE variable.  But the PROC SUMMARY code you posted was trying to transpose TWO variables.

 

If you only want to transpose ONE variable then PROC TRANSPOSE will work fine.

proc transpose data=have out=want(drop=_name_) delim=_;
  by no;
  id cd_rga jour_mois ;
  var mt_ea ;
run;

Result:

Tom_0-1704384878815.png

PS Make sure you have not accidentally set the VALIDVARNAME option to ANY or else those awful slashed in the JOUR_MOIS variable will get inserted into the generated variable names.

options validvarname=v7;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 435 views
  • 1 like
  • 4 in conversation