Multiple datasets reshape and calculate

[Emma2021]

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.

[PaigeMiller]

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.

[Emma2021]

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

[A_Kh]

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".  

[Emma2021]

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!

[Patrick]

"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.

[ballardw]

---

@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.

[PaigeMiller]

---

@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.

[Emma2021]

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.

[PaigeMiller]

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.

[Tom]

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

[Emma2021]

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

[Tom]

---

@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.