topic Re: one variable column from 10 columns in SAS Programming

PROC REPORT help

maple_tandoori — Thu, 13 Jul 2023 18:21:22 GMT

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?

Re: PROC REPORT help

Reeza — Thu, 13 Jul 2023 19:16:32 GMT

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?

Re: PROC REPORT help

maple_tandoori — Thu, 13 Jul 2023 19:50:04 GMT

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

Re: PROC REPORT help

Reeza — Thu, 13 Jul 2023 20:00:44 GMT

Use SCAN()

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

Re: PROC REPORT help

maple_tandoori — Mon, 17 Jul 2023 17:48:33 GMT

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

Re: PROC REPORT help

Reeza — Mon, 17 Jul 2023 20:36:20 GMT

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;

Re: PROC REPORT help

maple_tandoori — Mon, 17 Jul 2023 20:06:45 GMT

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

one variable column from 10 columns

maple_tandoori — Mon, 17 Jul 2023 20:29:02 GMT

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?

Re: one variable column from 10 columns

PaigeMiller — Mon, 17 Jul 2023 20:30:47 GMT

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?

Re: PROC REPORT help

Reeza — Mon, 17 Jul 2023 20:31:16 GMT

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

Re: one variable column from 10 columns

maple_tandoori — Mon, 17 Jul 2023 20:52:31 GMT

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

Re: PROC REPORT help

maple_tandoori — Mon, 17 Jul 2023 20:56:26 GMT

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

Re: one variable column from 10 columns

PaigeMiller — Mon, 17 Jul 2023 20:58:47 GMT

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.

Re: PROC REPORT help

Reeza — Mon, 17 Jul 2023 20:59:06 GMT

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

Re: PROC REPORT help

maple_tandoori — Mon, 17 Jul 2023 21:05:25 GMT

@Reeza

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

you are the best