topic Reading certain number of variables according to a condition in SAS Programming

Reading certain number of variables according to a condition

skjhzzang — Tue, 26 Nov 2019 11:25:46 GMT

For example below

Peter 2 14 16

James 3 17 19 18

Max 1 7

Logan 5 19 15 11 9 18

…

First variable is Name, Second variable is number of visit and others are amounts.

Peter visited twice so there are 2 amount variables and James visited three times so there are 3 amount variables.

~~I want to create a single data step that can read different number of variables according to a condition (# of visits).~~

~~And also sum of amount variables for each person.~~

Is it possible to just ignore reading Amount variables and just calculate sum of amount variables for each person and running total of the total amount variables for each person in a single data step?

Ex.)

Name TotalAmt Runtotal

Peter 30 30

James 54 84

Max 7 91

Logan 72 163

...

Re: Reading certain number of variables according to a condition

Patrick — Tue, 26 Nov 2019 05:07:35 GMT

You can't have a varying number of variables per row. But you can organize your data in a long format and you can have a varying number of rows per name. That's what the code below does.

It's also much easier to work with data in a long format (less variable, more rows). Many of the SAS Procedures then allow you to easily aggregate such data for analysis and reporting purposes.

data sample(drop=_:);
  infile datalines truncover;
  input name $ n_vals @;
  do _i=1 to n_vals;
    input amt @;
    output;
  end;
  datalines;
Peter 2 14 16
James 3 17 19 18
Max 1 7
Logan 5 19 15 11 9 18
;

proc print data=sample;
run;

proc sql;
  select 
    name,
    amt,
    sum(amt) as total
  from sample
  group by name
  ;
quit;

Re: Reading certain number of variables according to a condition

mkeintz — Tue, 26 Nov 2019 05:25:50 GMT

So, do you want to produce a different number of observations for each name (one obs per visit)? That's what @Patrick has shown you.

Or do you want one observation per name? If would have differing number of non-missing values for a fixed set of variables (where the fixed set would be sufficient to accommodate the largest number of visits in your sample).