topic Re: Summary over time in SAS Programming

Summary over time

ms400000 — Wed, 04 Nov 2020 17:40:31 GMT

I am trying to run a frequency summary for 4 categorical variables that change over time. They have the same possible categories but the freq could change depending on the variable. Is it possible to get a count all in one table so I can easily see how a category changed between them? They have about 600 categories so I don't want a crosstab of all the different combinations. Some categories may go to or from N=0 between var1 and var4. I looked at proc tabulate but I don't think it's quite what I want. Possible?

var1 var2 var3 var4

cat1 N N N N

cat2 N

cat600 N

Any help would be appreciated!

Re: Summary over time

PaigeMiller — Wed, 04 Nov 2020 17:42:46 GMT

Show us a portion of the original data.

You talk about time, but you don't show a time variable? Also, frequency of what?

Re: Summary over time

ms400000 — Wed, 04 Nov 2020 17:51:21 GMT

An equivalent example to my data would be where someone lives at different points of time.

id var1 var2 var3 var4

1 Ajax Ajax Ajax Brampton

2 Regina Navan Navan Navan

Re: Summary over time

ms400000 — Wed, 04 Nov 2020 17:53:30 GMT

And I would like the number of individuals in each city (a single row is a single individual)

Re: Summary over time

PaigeMiller — Wed, 04 Nov 2020 17:55:27 GMT

I'm afraid that there's still a disconnect here between your original problem statement and the data you show.

How do you compute the value of N in your desired output as shown in your original problem statement? N is a single number, I don't see where it comes from given the data you just showed. Please explain the logic of going from the input data you showed to the output table you showed.

Re: Summary over time

ms400000 — Wed, 04 Nov 2020 17:59:58 GMT

Sorry, cat1 - cat600 would be the different cities that are in my dataset. In var1 there were N1 people in Ajax, N2 people in Regina etc. Essentially I want a one way freq for each of var1 to var4 but in a single table and aligned when there may not be counts for all cities in all variables.

Re: Summary over time

PaigeMiller — Wed, 04 Nov 2020 18:20:04 GMT

Thanks.

PROC FREQ will compute the frequencies of each city at each point in time. From there, you'd probably want to merge the outputs into a single data set, merged by city, and then do a PROC PRINT.

Re: Summary over time

ballardw — Wed, 04 Nov 2020 18:42:05 GMT

Instead of pulling one detail after another please provide a small example data set, likely only 5 to 10 variables and a few values, small enough that you can show us the desired result for the example data.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 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.

I am thinking that this may be wanting a transpose and then use the name of the variables as a report variable such as:

data have;
   input var1 var2 var3 var4;
datalines;
12  13 12 5
5   5  6  12
3  12  12 8
;

data trans;
   set have;
   array v var1-var4;
   do i=1 to dim(v);
      name = vname(v[i]);
      cat  = v[i];
      output;
   end;
   keep name cat;
run;

proc tabulate data=trans;
   class name cat;
   table cat, name*n=' '
        misstext=' ';
run;