Multiple datasets reshape and calculate

[Tom]

---

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

[ballardw]

---

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

[Tom]

---

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

[Emma2021]

.

[Tom]

---

@Emma2021 wrote:
Can you loop over datasets ? The 2 other parameters would be renamed -so -if you can loop over datasets and by transposing variables etc would be the same variable as one in the first dataset.

---

You can whatever you want, but you need to be SPECIFIC about what you have and don't have.

If you have pattern in the names of the files than you can probably just use a DO loop or a %DO loop to drive the process.

If you have a dataset with the list of datasets then use that to drive the process.

[Tom]

If you have a variable named DOCTOR with values like 12345 and another variable named DOCTOR_NAME with values like 'John Smith'  then it should work to use one in the ID statement and the other in IDLABEL statement.

But the values need to be one to one.

[Tom]

When you have an error post the lines from the SAS log that show the code and the error message in context.

Remember to use the Insert Code or Insert SAS Code buttons on the editor menu bar when attaching text to your message so that the forum does not try to interpret the text as paragraphs.