How do I append files with different formats

[icipeb]

Good day SAS community members.

I am creating files using "proc surveymeans" and the "ods output Domain" feature.

Each file uses a different format defined in a proc format step.

I then want to append the files.

I am able to successfully accomplish this but it seems the format of each appended file is then lost and the resulting file uses only the format of the first file. By that I mean all records indicate an age of "1 year old (<2 years old)" or "All other ages ". Records that correspond to the "2 & 3 years old (2 to <4 years old)" groups also are shown as "1 year old (<2 years old)".

How can I ensure the formats of each file is maintained in the final file (file MAASPFile1to6 in the included SAS code).

I am including the pieces of code I believe are relevant and may help you guys better understand the issue I am facing.

Thanks in advance for your help!

Erik

proc format	;

value Age_1to2_F
1  		=	"1 year old (<2 years old)"
other	=	"All other ages"	;

value Age_2to3_F
2,3		=	"2 & 3 years old (2 to <4 years old)"
other	=	"All other ages"	;

run ;

proc surveymeans data=ZeData.SumAmtPerASPGroupperRespDRIwB	 varmethod=brr NOBS MEAN STDERR CLM CV	plots = none varheader = LABEL	;

title1	"Mean Amount Consumed (g) Per Food Category & Tier"	;
var	AmtT1ASPCat1
	AmtT2ASPCat1
	AmtT3ASPCat1
	AmtAllTASPCat1	;

domain	&MyDomn	; 

weight wts_s			;
repweight bsw1-bsw500	;

&Myfmt	&MyDRI	&MyDRIMat	;		

ods output	Domain = MAASPFile&MyFile	;

run	;

%let MyFile	= 5	;
%BigMac(MyDomn	=DHH_AGE,
		Myfmt	=format,
		MyDRI	=DHH_AGE,
		MyDRIMat=Age_1to2_F.)	;

%let MyFile	= 6	;
%BigMac(MyDomn	=DHH_AGE,
		Myfmt	=format,
		MyDRI	=DHH_AGE,
		MyDRIMat=Age_2to3_F.)	;

data	MAASPFile1to6	;
	set	MAASPFile5
		MAASPFile6		;

run ;

[PaigeMiller]

In SAS data sets, you can have only one format assigned to a variable. So, in the final data set, if you have combined two data sets with variables having the same name, then it will have only one format.

A workaround would be to give the variables different names in the combined file, and then each variable can have its own format.

[icipeb]

Thank you so much for your response, Paige.

It's helpful for me to know precisely what the source of the problem is.

Erik

[icipeb]

Here is an idea. Not a very elegant one and it may be silly but...

What f I exported each individual file to Excel, where values such as "2 & 3 years old (2 to <4 years old)" would then appear as text, then re-imported them into SAS and then appended them?

Erik

[yabwon]

Analyse my example and you can avoid excels.

[icipeb]

Thanks for the help!

I will dig into your example.

Erik

[PaigeMiller]

---

@icipeb wrote:

Here is an idea. Not a very elegant one and it may be silly but...

What f I exported each individual file to Excel, where values such as "2 & 3 years old (2 to <4 years old)" would then appear as text, then re-imported them into SAS and then appended them?

Erik

---

Never a good idea to use Excel as an intermediate step. In fact, that's NEVER in capital letters. Excel should be avoided like the plague in your SAS programs. (Perhaps at the end, exporting to Excel is necessary, but not in the middle).

SAS can do all the manipulations you want. In each data set, create a new variable which contains the formatted text (not the formatted number). Then when you combine the data sets, you can have one column with the formatted text appearing the way you want.

In each data set, you can use this to create the new character variable

new_variable = vvalue(variable);

Please note: this will have certain drawbacks compared to the solution of keeping things as numeric, particularly that your text strings will not sort in numerical order.

[icipeb]

Ahhh!

vvalue, now that could certainly solve my problem! Turn all those formatted values into text and use the new column instead.

Thanks a bunch and I promise to heed your advice regarding Excel and SAS. 🙂

Erik

[yabwon]

In short words: you can't. 

Format is property of whole variable and not for a particular observation value. 

But you could, with some additional programming effort, solve it. Look at this example and try to resolve it:

/* prepare formats */
proc format;

value firstFormat (default=20)
1 = "First 1"
2 = "First 2"
3 = "First 3"
other = "First Other"
;

value secondFormat (default=20)
1 = "Second 1"
2 = "Second 2"
3 = "Second 3"
other = "Second Other"
;


/* additional format which maps datsets names to formats */
value $ selectFormat 
"FIRSTDATA"="FIRSTFORMAT."
"SECONDDATA"="SECONDFORMAT."
other="BEST32."
;
run;

data firstData;
do x = 1 to 4;
  output;
end;
format x firstFormat.;
run;


data secondData;
do x = 1 to 4;
  output;
end;
format x secondFormat.;
run;

data thirdData;
do x = 1 to 4;
  output;
end;
run;


data combined;

  set firstData secondData thirdData INDSNAME=INDS;

  length new_text_x $ 50;
  fornatTxT=put(scan(INDS,-1,"."),$selectFormat.);
  new_text_x = putn(x,fornatTxT); /* putN uses format in the execution time */

run;
proc print;
run;

Bart

[icipeb]

Hi, yabwon.

Your response contains a lot of code new to me.

If I understand well, variable INDS contains the name of each file I am appending and a different format is assigned depending of each file is being appended.

What I don't understand is where in your code formats are assigned to variables. It seems your code assigns format to files not variables.

I appreciate your response and how much you are helping me learn.

Erik 

[Tom]

No formats are attached to any variables.

It is using the NAME of the dataset to determine which FORMAT to USE when generating a NEW CHARACTER variable.

[ballardw]

Add a second variable that has the same values as your DHH_age variable, assign the other format to that variable, use both variables in the Domain. Your current macro would be clumsy for this approach as you don't have a way to provide two formats in the macro code. But if the formats are assigned to the variable permanently then they are the default.

Or lobby SAS to provide MULTILABEL format functionality into Proc Surveymeans ...

Then you could use one format such as

proc format ;
value age_ml (multilabel)
1  		=	"1 year old (<2 years old)"
2-high	=	"All ages other than 1"	;
2,3		=	"2 & 3 years old (2 to <4 years old)"
1,4,5,6,7,8	=	"All ages other than 2 and 3"	
;
run;

The way that multilabel formats work each group you want in reports need to be defined on one line. So if your age range goes higher than 8 you need to add those ages.

Only a few procedures can use multilabel formats though. I don't know the range of your age variable but you could see the results possible with:

proc format ;
value age_ml (multilabel)
1  		=	"1 year old (<2 years old)"
2-high	=	"All ages other than 1"	;
2,3		=	"2 & 3 years old (2 to <4 years old)"
1,4,5,6,7,8	=	"All ages other than 2 and 3"	
;
run;

proc means data=ZeData.SumAmtPerASPGroupperRespDRIwB ;
   class DHH_AGE/mlf ;
   format DHH_AGE age_ml;
   var AmtT1ASPCat1
	AmtT2ASPCat1
	AmtT3ASPCat1
	AmtAllTASPCat1;
   weight wts_s;
run;

[Reeza]

data MAASPFile&MyFile;
set MAASPFile&MyFile;

&MyDRI_char = put(&MyDRI, &MyDRIMat);
run;

format &MyDRI 8.;
run;

Add that into your macro to save the variable as variableName_char with a character format and remove the format from the numeric variable. That should get you the desired results. Keep both variables so that you can sort correctly.