PROC REPORT help

[maple_tandoori]

Hello hope you are doing well. I need some help with regards to performing a certain task on proc report. Consider the follow output table stored as data = mytable:

County_code      State_N      HP1_N     HP1%   HP2_N  HP2%   HP3_N  HP3%

Arizona                  2000               500        2%        500           3%        1000              1% 
Smith                        300                5          0.5%      2            1.5%       2               0.5%
NewPlace                 150               10       5%            50            2%         50   2%

Consider that i want to create a long table format (tabular format>>>>>)

Country_code     state_n     hp_name    hp_n      hp%
Arizona                  2000           hp1              500      2%

Smith                     300             hp1                5          0.5%

NewPlace             150              hp1                10          5%

Arizona                  2000          hp2             500       3%

Smith                      300             hp2              2            1.5%
NewPlace              150             hp2               50       2%

Arizona                 2000         hp3                  1000          1%

Smith                     300           hp3                  2                  0.5%
NewPlace              150            hp3                  50                  2%


Does this make sense, can someone help me figure out perhaps how to convert this table a to b using proc report? Or is the wrong function to use?

[Reeza]

Proc report is the wrong procedure to use, proc transpose is one option. A data step is the easiest.

Your variable names don't quite make sense so you'll need to adjust the code to your actual variable names.

data want;
set have; 
array _n(*) hp1_n hp2_n hp3_n;
array _p(*) hp1-hp3;

do i=1 to dim(_n);
hp_n = _n(i);
hp_pct = _p(i);
hp_name = vname(_p(i)); * may need to extract, percent cannot be in name so not sure what actual name is correct;
output;
end;

keep country_code state_name hp_name hp_n hp_pct;
run;

---

@maple_tandoori wrote:

Hello hope you are doing well. I need some help with regards to performing a certain task on proc report. Consider the follow output table stored as data = mytable:

County_code      State_N      HP1_N     HP1%   HP2_N  HP2%   HP3_N  HP3%

Arizona                  2000               500        2%        500           3%        1000              1% 
Smith                        300                5          0.5%      2            1.5%       2               0.5%
NewPlace                 150               10       5%            50            2%         50   2%

Consider that i want to create a long table format (tabular format>>>>>)

Country_code     state_n     hp_name    hp_n      hp%
Arizona                  2000           hp1              500      2%

Smith                     300             hp1                5          0.5%

NewPlace             150              hp1                10          5%

Arizona                  2000          hp2             500       3%

Smith                      300             hp2              2            1.5%
NewPlace              150             hp2               50       2%

Arizona                 2000         hp3                  1000          1%

Smith                     300           hp3                  2                  0.5%
NewPlace              150            hp3                  50                  2%


Does this make sense, can someone help me figure out perhaps how to convert this table a to b using proc report? Or is the wrong function to use?

---

[maple_tandoori]

Hello @Reeza ,

Thanks for this explanation, and the table looks almost like how i want it to be! I really appreciate you taking the time to help me out:

What would be the best way to extract the hospital name to create the new variable. For example consider the following variables

kgh_n  cheo_n  tgh_n kgh_pct cheo_pct tgh_pct

so essential want it as

hosital name

kgh
kgh
kgh
cheo
cheo
cheo
tgh
tgh
tgh

[Reeza]

Use SCAN()

hospital =scan(vname(....), 1, "_");

[maple_tandoori]

@Reeza 

Thank you for getting back to previously and helping me with the code. I was still struggling to fill in the hospital name into a vertical column if the original names appear as a row.
"kgh_n  cheo_n  tgh_n kgh_pct cheo_pct tgh_pct".
Not sure how to use the proc scan function properly for this. Would really appreciate your help.

The problems it I wanted to create a long tabular table, which has hospital appear in the column. Not the original table was like this:

County_code      State_N     kch_n     kch_pct   tgh_n  tgh_pct  cheo_n     cheo_pct

Arizona                  2000               500        2%        500           3%        1000              1% 
Smith                        300                5          0.5%      2            1.5%          2                     0.5%
NewPlace                 150               10       5%            50            2%         50                2%

Consider that i want to create a long table format (tabular format>>>>>)

Country_code     state_n     hp_name    hp_n      hp%
Arizona                  2000           kgh             500      2%

Smith                     300             kgh                5          0.5%

NewPlace             150              kgh               10          5%

Arizona                  2000          tgh             500       3%

Smith                      300             tgh              2            1.5%
NewPlace              150             tgh               50       2%

Arizona                 2000         cheo                  1000          1%

Smith                     300           cheo                 2                  0.5%
NewPlace              150            cheo                 50                  2%

[Reeza · Accepted solution]

Not a proc, it's a function.

data want;
set have;
array _n(*) hp1_n hp2_n hp3_n;
array _p(*) hp1-hp3;

do i=1 to dim(_n);
hp_n = _n(i);
hp_pct = _p(i);
hp_name = scan(vname(_p(i)), 1, "_"); * may need to extract, percent cannot be in name so not sure what actual name is correct;
output;
end;

keep country_code state_name hp_name hp_n hp_pct;
run;

[maple_tandoori]

@Reeza 
Thank you sir!
It just that i dont know how to make the scan function work to create the column as i mentioned (with hospital name). Hope that makes sense.

[Reeza]

Did you see the updated code? It should work, assuming your data is as shown.
FYI - not a sir.

[maple_tandoori]

@Reeza 
My apologizes! Meant no disrespect. 
Yea for some reason i can't get the scan function work on the multiple variables. This again what my code looks like, thanks to your help. Adding the hospital name is important so that i can proc sort by the hospital, which will help in the final output. Still trying to figure it out.

data FINAL_TABLE;
set NEW_CASES_TABLE;

array _n(*) kgh_N CHEO_N tgh_N;
array _p(*) kgh_PCT CHEO_PCT tgh_PCT ;

do i=1 to dim(_n);
hp_n = _n(i);
hp_pct = _p(i);

output;
end;

keep country_NAME state_N hp_n hp_pct;
run;

[Reeza]

You missed the line I highlighted in blue in my code....

[maple_tandoori]

@Reeza 

Thank you! im sorry i dont know what i was doing. This works.

you are the best

[maple_tandoori]

Hello everyone,

A relatively new user on sas, can someone help me do this below:

so i have 10 columns, i want to create a single column "hospital_name" in SAS. Lets says if there is value in two columns then the new variable is given a variable of 1... if there is a value in the next two columns then the new variables is given a variable of 2... and so on.... im trying to make a single long column... what would be the best way to do this in SAS?

[PaigeMiller]

Please show us a few concrete examples.

Normally, I think combining these values into a single variable is a poor idea. What are you going to do with this once you combine these variables?

[maple_tandoori]

@PaigeMiller 

Thanks for replying. The problem is that im trying to create the tabular table from table 1 below. In which hospital name appears in a singular column like in table 2. As you can probably see im trying to come up with the best way to create this new variable based if for example there is a value in (kch_n, kch_pct) =1 and if (tgh_n, tgh_Pct)=2 and so on. I was able to create the tabular table use an array and do loop, but was having touble creating the new hospital variable:

Table 1

County_code      State_N     kch_n     kch_pct   tgh_n  tgh_pct  cheo_n     cheo_pct

Arizona                  2000               500        2%        500           3%        1000              1% 
Smith                        300                5          0.5%      2            1.5%          2                     0.5%
NewPlace                 150               10       5%            50            2%         50                2%

table 2
Country_code     state_n     hp_name    hp_n      hp%
Arizona                  2000           kgh             500      2%

Smith                     300             kgh                5          0.5%

NewPlace             150              kgh               10          5%

Arizona                  2000          tgh             500       3%

Smith                      300             tgh              2            1.5%
NewPlace              150             tgh               50       2%

Arizona                 2000         cheo                  1000          1%

Smith                     300           cheo                 2                  0.5%
NewPlace              150            cheo                 50                  2%

this is my code where im trying to add the new created variable

data FINAL_TABLE;
set NEW_CASES_TABLE;

array _n(*) kgh_N CHEO_N tgh_N;
array _p(*) kgh_PCT CHEO_PCT tgh_PCT ;

do i=1 to dim(_n);
hp_n = _n(i);
hp_pct = _p(i);

output;
end;

keep country_NAME state_N hp_n hp_pct;
run;

[PaigeMiller]

Unfortunately, I don't understand what you are doing. I do not see a clear connection between the two tables. Nor do I see why the new variable is kgh for some rows and tgh and cheo in other rows. Please explain this part in a lot more detail, step-by-step.

Also, you did not answer my question: "What are you going to do with this once you combine these variables?" This is critical, there may be easier ways than whatever it is you are doing, but we don't know what you plan to do with this variable once it is created. If we knew what you are going to do with this variable once it is created, we could suggestion potentially easier/better ways to get there.