Get all SOC in proc format

[tarheel13]

Is there a way to get all the SOCs into a proc format step? 

Subject	aeterm_Soc	aeterm_PT
1	Cardiac disorders	arrhythmia
1	Respiratory disorders	Pulmonary embolism
2	Vascular disorders	hypertension
2	Blood disorders	Anemia
3	Blood disorders	Anemia
3	Vascular disorders	DVT
4	Blood disorders	Lymphopenia
4	Infections and infestations	Septic shock
5	Blood disorders	Lymphopenia
6	Respiratory disorders	Pneumothorax
7	Psychiatric disorders	Agitation

Here is some sample data. I am making an AE table using proc report and preloadfmt. This is what I do but I have to keep adding more to the proc format as more SOCs present in the data. Is there a way to make this more dynamic? 

proc sql;

select distinct aeterm_soc into :soc1 -

from adae; quit;

proc format;

value $ descr (multilabel notsorted)

"&soc1","&soc2","&soc3"="All Events"

"&soc1"="&soc1"

"&soc2"="&soc2"

"&soc3"="&soc3"

;

run;

There are 27 SOCs in MedDRA. Is there a better way to do this so I wouldn't have to hypothetically write "&soc1"="&soc1" all the way to "&soc27"="&soc27"?

Thanks!

[PaigeMiller]

What does this partial data set have to do with your problem?

Where does &soc1 (and all the others) get defined?

Why would you need a format "&soc1"="&soc1", essentially assigning to the text string a format that is equal to this exact text string?

[tarheel13]

To give context of the problem I am trying to solve! 

I included the proc sql step where the macro variables are assigned!

And because I have to count the AEs by SOC and PT and I needed a row for "All Events". 

[PaigeMiller]

Formatting of the sort you are talking about is not required to do counting.

PROC FREQ or PROC REPORT or PROC TABULATE or PROC MEANS or PROC SUMMARY does all of this on your data set without creating these formats, and will create totals across all events, again without these formats being created.

[tarheel13]

Yes, if you read the OP, I said that I do use preloadfmt with proc report. This is a step that I do before that. I know what proc freq does. I guess you don't really understand what I was asking. I have a table shell that I have to work off of. 

[PhilC]

@PaigeMiller is correct.  Preloaded formats and multilabel formats are two different features that operate with many procedures.  How I am, and perhaps how Paige may be, thinking is that one would use PROC TABULATE to summarize the total.  So PROC TABULATE is powerful and lets just keep it at that.

proc sql;
  create table SOC as
    select distinct cause as aeterm_soc 
    from SASHELP.failure; 
quit;


data SOC;
   set SOC end=eod;

   start=aeterm_soc;
   end  =aeterm_soc;
   label=aeterm_soc;

   retain fmtname 'descr' 
          type 'C' 
          HLO "M";

   output; /*Regular entry*/

   Label="All Events";
   output; /*multilabel "All Events"*/

;
run;

proc format library=work cntlin=SOC;
run;

Title "with multilabel formats";
PROC TABULATE DATA=SASHELP.FAILURE (obs=4)	;
  FORMAT Cause $descr.;
	CLASS Cause /	  PRELOADFMT MLF;
	TABLE Cause,N /printmiss		;
RUN;

Title "with out multilabel formats";
PROC TABULATE DATA=SASHELP.FAILURE (obs=4)	;
  FORMAT Cause $descr.;
	CLASS Cause /	  PRELOADFMT ;
	TABLE (Cause all="All Events"),N /printmiss		;
RUN;

[tarheel13]

Yeah the table is actually a little more detailed than just SOC and PT though. I have to also use AESEV and AEREL. I think it may be more time researching a workaround for this than just writing out &soc1 up to &soc27.

[PhilC]

There is this example that show how to convert a dataset into a format.  It does not address your multilabel features. 

SAS Help Center: Creating a Format from a CNTLIN= Data Set

To make the simple (non multilabel format) you'll do the SAS equivalent of making your given dataset look like this (ignoring repeats): 

Start	End	Label
Cardiac disorders	Cardiac disorders	Cardiac disorders
Respiratory disorders	Respiratory disorders	Respiratory disorders
Vascular disorders	Vascular disorders	Vascular disorders
Blood disorders	Blood disorders	Blood disorders
Blood disorders	Blood disorders	Blood disorders
Vascular disorders	Vascular disorders	Vascular disorders
Blood disorders	Blood disorders	Blood disorders
Infections and infestations	Infections and infestations	Infections and infestations
Blood disorders	Blood disorders	Blood disorders
Respiratory disorders	Respiratory disorders	Respiratory disorders
Psychiatric disorders	Psychiatric disorders	Psychiatric disorders

Then you'll add other columns, like "FMTNAME".  If you can make this happen then there are a couple of steps to go from a regular format to a multilabel format.