How to format stacked freq table

[superbug]

Hello SAS experts,

In the attachment, the data I have is called "want_final".

After running the following code, I get "resp_freq", please see the attached on how does this data look like. 

ODS output onewayfreqs=resp_freq;
proc freq data=want_final;
table pair_:;
run;

Then I ran the following code, and get "resp_freq1", please see the attached of how does this data look like. 

data resp_freq1;
length Pair $32. category $50.;
set resp_freq;
Pair=scan(table, 2);
category=strip(trim(vvaluex(Pair)));
keep Pair category frequency percent;

label Pair='Pair'
Category='Category';
run;

I want to insert "item_id" and "itempos" variables in the "resp_freq1", please help how should I edit the above code, so that to get the table I want. Table I want is the last table in the attache document.

Thanks much! 

[ballardw]

What type of variables are item_id and itempos supposed to be, character (how long) or numeric?

Where are the values to come from? Or do you just want missing values to fill in latter some how?

If you want missing values:

data resp_freq1;
length item_id $ 15 itempos 8.;
length Pair $32. category $50.;
set resp_freq;
Pair=scan(table, 2);
category=strip(trim(vvaluex(Pair)));
keep Pair category frequency percent;

label Pair='Pair'
Category='Category';
run;

Will create a first variable named Item_id that will hold up to 15 characters and a numeric variable itempos in the second position expecting a numeric value.

[superbug]

@ballardw 

Thanks for your reply.

I want "item_id" and "itempos" to be character variable and "itempos" to be numeric as you defined. I don't want those two variable to be missing. These two variables are in the FIRST table ("have" ) in the attached doc file. I want those values to be filled in the final table. 

Maybe the name of tables a little confusing in my first post. please see the new attached doc for table name and final tables I want.

The following are the corresponding SAS code, please help on how to inserted "item_id" and "itempos" variables with FILLED values 

ODS output onewayfreqs=resp_freq;
proc freq data=have;
table pair_:;
run;

data want;

length item_id $15. itempos 8.;
length Pair $32. category $50.;
set resp_freq;
Pair=scan(table, 2);
category=strip(trim(vvaluex(Pair)));
keep Pair category frequency percent;

label Pair='Pair'
Category='Category';
run;

[ballardw]

---

@superbug wrote:

@ballardw 

Thanks for your reply.

I want "item_id" and "itempos" to be character variable and "itempos" to be numeric as you defined. I don't want those two variable to be missing. These two variables are in the FIRST table ("have" ) in the attached doc file. I want those values to be filled in the final table. 

Maybe the name of tables a little confusing in my first post. please see the new attached doc for table name and final tables I want.

Since the way you create the data set Resp_freq does not have the variables you want I think you are going to have to share an example of your data and what the final result is supposed to look like.

How would we know which value you want for those two variables? If you have more than two levels for any of those added variables there really isn't any logical connection between the freq output an those variables.

Please provide any rules for selecting the values of item_id and item_pos.

Best would be to provide a small sample of the starting data and the desired final output as data step code.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

[superbug]

@ballardw 

I attached my data "have". In the "have" data , variable "pair_1_2_3_4" are test takers 1, 2, 3, 4 responses on item  "PPP002648" to " PPP031726" (i.e, itempos from 1 to 250)

Similarly variable "pair_1_2_3_5 " are test takers 1,2,3,5 responses on those 250 items.

The first result I want is the freq of "pair_:" on each item. 

You mentioned "the way you create the data set Resp_freq does not have the variables you want", so in the following code, could you please provide instructions on how to incorporate "item_id" and "itempos" variables?

ODS output onewayfreqs=resp_freq;
proc freq data=have;
table pair_:;
run; 

[ballardw]

Here is a very small example data set and some proc freq code:

data example;
   input a $ b c;
datalines;
a 1 2
a 1 3
a 1 5
a 2 3
a 2 5
a 2 2
b 1 1
b 1 1
b 2 2
b 2 2
b 3 1
c 1 4
c 2 1
;

proc freq data=example;
   tables b c;
run;

Running that proc freq generates output like

                              Cumulative    Cumulative
b    Frequency     Percent     Frequency      Percent

1           6       46.15             6        46.15
2           6       46.15            12        92.31
3           1        7.69            13       100.00


                              Cumulative    Cumulative
c    Frequency     Percent     Frequency      Percent

1           4       30.77             4        30.77
2           4       30.77             8        61.54
3           2       15.38            10        76.92
4           1        7.69            11        84.62
5           2       15.38            13       100.00

So, from that output, which values of the variable A should be attached to which values of B and C in the above output?

The more repeated values you have the harder it is going to be to decide which item_id goes with which frequency count result.

Anything that adds variables in proc freq is going to change your counts.

So, Show some example data, which you haven't because I have to make decisions on how to turn your CSV into a SAS data set and my choices may not be compatible with your data set.

Then show the desired output.

And it may be that if you did any processing to create that data was just making your data more complex.