topic Multiple datasets reshape and calculate in SAS Programming

Multiple datasets reshape and calculate

Emma2021 — Thu, 27 Apr 2023 20:55:50 GMT

I have multiple longitudinal datasets. I want to transpose by one variable each of those datasets. The variable has 6 categories—then calculate the average weight (numeric data) by groups for each column (6 above categories).
How can I do that? Better a macro that I can change dataset name, transposing variable (this variable name is different for each dataset), and the weight (sometime the weight is as numeric and sometimes as character and the name is different —sometime weight and sometimes wgt). Thank you.

Re: Multiple datasets reshape and calculate

PaigeMiller — Thu, 27 Apr 2023 21:22:22 GMT

If you are computing means, it seems like you want a report rather than a reshaped data set.

I would recommend PROC REPORT.

Providing a portion of your data would help as then I (and others) could provide actual code.

You can't have a weight that is character.

Re: Multiple datasets reshape and calculate

Emma2021 — Thu, 27 Apr 2023 21:33:07 GMT

No report, but creating person level weight data from multiple time measured weights.

Re: Multiple datasets reshape and calculate

A_Kh — Thu, 27 Apr 2023 23:20:19 GMT

Looks like you need just average weight of all subjects per timepoint. Does sashelp.cars example reflects your data?

proc sql;
	create table want as 
		select *, mean(weight) as Average
			from sashelp.cars
		group by type
	order by type;
quit;

If the weight comes as character datatype (which is abnormal), use "mean(input(weight, best.)) as Average".

Re: Multiple datasets reshape and calculate

Emma2021 — Fri, 28 Apr 2023 01:33:25 GMT

No. For example, there are 5 doctors who took the weight measure - each doctor took multiple times. So, first it has to be reshaped by those doctors as wide and then calculate average weight for each doctor. So-it has to be reshaped first. Also, I prefer macro since there are many datasets (with 3 parameters as I mentioned). Thank you!

Re: Multiple datasets reshape and calculate

Patrick — Fri, 28 Apr 2023 02:06:52 GMT

"So, first it has to be reshaped by those doctors as wide..."

That's exactly what SAS BY processing is for. Have a look into Proc Means. You would have the doctor variable either in the CLASS or BY statement.

With Proc SQL similar things can be done using the GROUP BY statement.

It's most of the time better to keep the data in a narrow structure.

Re: Multiple datasets reshape and calculate

ballardw — Fri, 28 Apr 2023 02:17:09 GMT

@Emma2021 wrote:
No. For example, there are 5 doctors who took the weight measure - each doctor took multiple times. So, first it has to be reshaped by those doctors as wide and then calculate average weight for each doctor. So-it has to be reshaped first. Also, I prefer macro since there are many datasets (with 3 parameters as I mentioned). Thank you!

Please go back to your first post. Read it closely.

Do you see any mention of DOCTOR? I don't. When you do not accurately describe the data (HINT: provide actual data) and a clear description of all of the elements of a task it doesn't help.

If you need to have the mean of some measures by the combination of Doctor and "person" then Proc Means/ summary with a class statement will do that. No reason to make a wide data set.

Here is a brief example of calculating a few means by groups of other variables with a data set that you should have in your SAS install.

Proc summary data=sashelp.class;
   class sex age;
   var height weight;
   output out=example mean= ;
run;

The above calculates the means for 1)all observations in the data set 2)each level of age 3) each level of sex and 4) each combination of sex and age present in the data. Note that there are two variables in the output also added: _freq_, which is the number of observations used for each record and _type_ which aligns with which combination.

I show how to do multiple because your description changes. You can select which one makes sense for your specific use by selecting the correct _type_ value. IF you want the combinations of Sex and Age, which would be _type_=3 you could add the option NWAY to the Proc statement. Think of Sex as taking the role of your Doctor variable and Age as the person id.

And as @PaigeMiller said, a variable used to weight data cannot be character. In the case that you actually have a variable that should be treated as a weighting variable then when you fix the character issue fix all the name issues as well. You would have to do this before any "macro" to begin with.

If your data sets are similar enough otherwise then perhaps they should be combined before this summary. Unless there is yet another level of the problem you have not described. I would be wondering if some combinations of Doctor and person occur in different data sets (perhaps different time periods????) and should there be separate "means" just because they occur in different data sets.

Cleaning data to standardize things like variable names, types and lengths is something that should be done before any analysis or reporting. Try it. You'll find that everything goes much smoother later.

Re: Multiple datasets reshape and calculate

PaigeMiller — Fri, 28 Apr 2023 10:05:23 GMT

@Emma2021 wrote:
No report, but creating person level weight data from multiple time measured weights.

PROC SUMMARY can create a data set with averages by person (or even by multiple categories), but still you cannot have weights unless they are numeric. This is an absolute restriction, weights must be numeric.

Re: Multiple datasets reshape and calculate

Emma2021 — Fri, 28 Apr 2023 10:18:36 GMT

The datasets are long formatted by id and doctor type. The weight variable has to be averaged by doctor type but not long formatted -has to be wide formatted ( doctor types should be columns and IDs are as rows.

Note: I would like a macro since there are many are many datasets,
Doctor type variable not consistently named across all datasets as well as the weight variable.

Re: Multiple datasets reshape and calculate

PaigeMiller — Fri, 28 Apr 2023 10:25:47 GMT

Please show us a portion of one of these data sets. If the data is confidential, please make up a data set that represents the real problem. Please provide the data as working SAS data step code, which you can type in your self, or you can follow these instructions. Do not provide the data in any other form. REPEATING: DO NOT PROVIDE THE DATA IN ANY OTHER FORM.

Re: Multiple datasets reshape and calculate

Emma2021 — Fri, 28 Apr 2023 18:58:59 GMT

Re: Multiple datasets reshape and calculate

Tom — Fri, 28 Apr 2023 13:58:23 GMT

Use PROC SUMMARY to count/average.

data have;
  input ID :$13. Doctor :$8. Wgt;
datalines4;
Acura MDX 3.5
Acura MDX 5.5
Acura RSX 2 4
Acura RSX 2 4
Acura TS 4.4
Acura TS 6.4
Acura TL 6.2
Kin MDX 3.5
Kin MDX 5.5
Kin RSX 2 4
Kin RSX 2 4
Kin TS 4.4
Kin TS 6.4
Kin TL 6.2
Kin TL 3.5
;;;;

proc summary data=have nway ;
  class id doctor;
  var wgt ;
  output out=step1 mean= ;
run;

Then use PROC TRANSPOSE to convert to wide

proc transpose data=step1 out=weights ;
  by id;
  id doctor;
  var wgt ;
run;

proc transpose data=step1 out=counts prefix=num_;
  by id;
  id doctor;
  var _freq_;
run;

You could use a format to display the counts larger than 1 as YES.

proc format ;
  value multiple
    low-1 = 'NO'
    2-high = 'YES'
  ;
run;

data want;
  merge weights counts;
  by id;
  drop _name_;
  format num_: multiple.;
run;;

Result

Re: Multiple datasets reshape and calculate

Emma2021 — Fri, 28 Apr 2023 14:09:01 GMT

Thank you. I need a sas macro to loop over multiple datasets.

Re: Multiple datasets reshape and calculate

Tom — Fri, 28 Apr 2023 14:15:01 GMT

@Emma2021 wrote:
Thank you. I need a sas macro to loop over multiple datasets.

Macro code is used to generate SAS code (in general). So first figure out what SAS code you need to generate for one input. Then clarify what parts of that code will need to change to handle a different dataset. Replace those parts with macro variables. Get that to work. Then wrap the result into a macro that uses those macro variables as the input parameters.

Why do you have multiple datasets?

What do they represent?

How are they different?

If the structure of each dataset is the same then perhaps you just need to first combine them into one dataset and then run the same code as before only adding an extra BY/CLASS variable before ID. At that point I am not sure how much value is added by converting the program into a macro.

Re: Multiple datasets reshape and calculate

Emma2021 — Fri, 28 Apr 2023 18:59:38 GMT

Re: Multiple datasets reshape and calculate

Tom — Fri, 28 Apr 2023 14:26:48 GMT

@Emma2021 wrote:
I have received many Datasets that I need to create separate datasets from long to wide (maybe different hospitals).

Combine the datasets. Assuming there is already a variable that indicates the hospital.

data all;
  set hospital1 hospital2 ...... ;
run;

Now just add HOSPITAL in addition to ID and DOCTOR as the CLASS variables.

Re: Multiple datasets reshape and calculate

Emma2021 — Fri, 28 Apr 2023 19:00:38 GMT

Re: Multiple datasets reshape and calculate

Emma2021 — Fri, 28 Apr 2023 19:00:09 GMT

Re: Multiple datasets reshape and calculate

Tom — Fri, 28 Apr 2023 14:35:42 GMT

@Emma2021 wrote:
I need 3 parameters:
Input and output dataset name (added suffix such as 1 and 2 is fine), by variable to transpose, and variable (for car).

Thank you.

Take the existing code that works for ONE dataset. Say it is reading in from dataset HOSP1 and writing out dataset WANT1. Say the variable names that change are called ID and DOCTOR.

So take you code that works and replace every reference to HOSP1 with &INDSN and every reference to WANT1 with &OUTDSN. Do same thing for the variable names. Then add these four %LET statements before the code.

%let indsn=HOSP1;
%let outdsn=WANT1;
%let var1=ID;
%let var2=DOCTOR;

And run it again. Get it to work.

Now add a %MEND statement after the code. Replace the %LET statements with this %MACRO statement:

%macro mymacro(indsn,outdsn,var1,var2);

After the %MEND statement add this call to the macro:

%mymacro(indsn=HOSP1,outdsn=WANT1,var1=ID,var2=DOCTOR)

RUn it and get it to work.

Now to run other datasets just replicate the macro call and change the input dataset and output dataset names and if needed the variable names.

Re: Multiple datasets reshape and calculate

ballardw — Fri, 28 Apr 2023 14:37:54 GMT

@Emma2021 wrote:
No -should not combine -they should be separate and outputs datasets should be separate

Include a "hospital" variable, which could be the source dataset name when combining data.

Then in proc summary include that variable as a class (or if the resulting data set is large enough a BY variable)

And even with multiple hospitals/treatment facilities you can still have the same doctor see the same patient. So you still need to consider how those cases are to be considered.

Really, fix the data. Make sure the data sets have the same variable names, properties and types. THEN do any analysis.

Your insistence on a loopy macro trying to handle different variable names, changing types (your claim the weight/wgt variable is sometimes character) is a symptom, not a solution. If really needed, and no actual need has been demonstrated, after the summary step you can separate the data into different sets. And then any processing of those further data sets will much easier because then all the variables are the same and the same type. Otherwise you get to go through the same nonsense all over again to deal with each new summarized data set. Unless the whole purpose of specifying this macro approach is to create some sort of job security because no one else will be able to follow what was done and why...