topic Re: Creating a new column based on the frequency of a variable in SAS Programming

Creating a new column based on the frequency of a variable

hk1234 — Wed, 15 Feb 2017 12:07:32 GMT

Hi,

I am using sas 9.4 base

I want to measure the frequency of a each variable within a column (similar to proc freq) and then create a second column which states the frequency nxt to ac instance of the variable.

The data is from Facebook and has an individual row per action (like, comment, share) with an actor Id column (so if the actor has liked a post more than once it is on a separate row). So I want to rank each actor by the number of actions that he/she has taken.

This can be done with a proc freq report but I'm not sure how to create a column stating how many actions each actor has taken on every row.

Further it would be awesome if the time between action (each row is an action) can be measured on the actor level.... pls see the attached screenshot

Any help would be greatly appreciated!

-Harrison

Re: Creating a new column based on the frequency of a variable

Reeza — Wed, 15 Feb 2017 12:13:32 GMT

Use proc freq to get counts and then transpose to reshape the data to the form you want.

Re: Creating a new column based on the frequency of a variable

hk1234 — Wed, 15 Feb 2017 12:30:29 GMT

Can you post the code?

Re: Creating a new column based on the frequency of a variable

data_null__ — Wed, 15 Feb 2017 13:21:42 GMT

Your data is a picture. You need to post data that community members interested in helping you can use in a program. For example.

data fb;
   infile cards dsd missover;
   input (list of variable names)(informats);
   cards;
value,value,value,...
value,value,value,...
value,value,value,..
;;;;
   run;

Re: Creating a new column based on the frequency of a variable

mkeintz — Wed, 15 Feb 2017 13:25:15 GMT

Can you post what the input data looks like in SAS? And what you want the output data to look like?

I think you want for every record, two new variables

actor_freq: The overall frequency of the actorid, taken from proc freq output
time_lapse: Time since last action by the same actorid

Absent your posting testable date ...

Assuming your data are sorted by the time stamp UPDATEDDATE, then this untested code would be a useful approach: It uses hash objects name FREQ (taken from a data set made by proc freq) and PRIOR (built dynamically during the "DATA WANT" step):

proc freq data=have  noprint;
  tables actorid / out=lookupfreqs (keep=actorid count  rename=(count=actor_freq));
run;

data want;
  set have;
  if _n_=1 then do;
    if 0 then set lookupfreqs;
      declare hash freq(dataset:'lookupfreqs');
        freq.definekey('actorid');
        freq.definedata('actor_freq');
        freq.definedone();

    format prior_update datetime20.;
      declare hash prior();
        prior.definekey('actorid');
        prior.definedata('prior_update');
        prior.definedone();
  end;

  rc=freq.find();

  rc=prior.find();
  if rc=0 then time_lapse=updateddate - prior_update;
  prior_update=updateddate;
  rc=prior.replace();
run;

Notes:

I repeat, dataset HAVE is assumed to be sorted by time stamp.
You want to retrieve the frequency of total records by actorid, not by actorid/action_type. That's what the hash object FREQS holds, taken directly from proc freq output.
UPDATEDDATE is a sas datetime value.
You want the time_lapse since the last action of any type by the same actorid.
The hash object PRIOR, unlike FREQ, is dynamically built. When an actoirid is enountered for the first time, the statement "rc=prior.find()" will generate a non-zero rc, so no time_lapse can be calculated. But sebsequent find's for the same id will succeed (rc=0). This object is meant to hold one item per actorid, always containing the time stamp of the most recent prior action of that actorid.

One could also re-sort the dataset by actorid/updateddate, and then more easily (i.e. without a hash object) get time_lapse in a data step, but I assume your data would be large enough to make a proc sort costly.

Re: Creating a new column based on the frequency of a variable

ballardw — Wed, 15 Feb 2017 15:09:47 GMT

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 {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Creating a new column based on the frequency of a variable

hk1234 — Thu, 16 Feb 2017 14:32:37 GMT

Thanks for your help! Your solution worked perfectly