topic Re: Creating single variable from multiple variables in New SAS User

Creating single variable from multiple variables

sameer112217 — Sun, 09 Dec 2018 06:08:49 GMT

I have the following table

Year policy_no As_on_mar2016 As_on_Mar2015 As_on_Mar2014

2013 001 100

2013 002 200

2013 003 100

2014 001 200

2014 002 200

2014 003

2015 001 100

2015 002 300

2015 003 300

I want the final below output like this in one variable called net_amount from different variables above. I want As_on_Mar2014 in year 2013 against that policy, As_on_Mar2015 in 2014 and As_on_Mar2014 in 2015. Please note the policies are in tens of thousand in original data

Year policy_no Net_Amount

2013 001 100

2013 002 200

2013 003 100

2014 001 200

2014 002 200

2014 003

2015 001 100

2015 002 300

2015 003 300

I tried using arrays

data want;

set values;

arrays year (3) _temporary_ (2013 2014 2015);

arrays varf (3) As on Mar2014 As on Mar2015 As on Mar2016;

do i= 1 to 3;

if year = year[i] then net amount= varf

end;

drop (i);

output;

run;

It does not satisfy my conditions of both year and policyno because the policies in the original data I am using is in thousands compared to year which are 5 to 6 in distinct value so how do I use the loop conditions or other methods

Re: Creating single variable from multiple variables

novinosrin — Sun, 09 Dec 2018 06:12:41 GMT

data want;
set have;
netamt=coalesce(of As_on_mar2016--As_on_Mar2014);
run;

Re: Creating single variable from multiple variables

sameer112217 — Mon, 10 Dec 2018 08:57:54 GMT

Thanks for the prompt reply. What if the other columns have values and we dont want to sum that.

Is it possible to do array with if conditions for multiple policiy_no

Re: Creating single variable from multiple variables

novinosrin — Mon, 10 Dec 2018 09:47:20 GMT

Can you post a clear requirement and sample plz

Re: Creating single variable from multiple variables

sameer112217 — Tue, 11 Dec 2018 09:34:07 GMT

@novinosrin

I mean in the below example lets assume if there are values in other columns like for first rows. I just want 100 in the first row values for net_amount and ignore the value 200 in the first rows. similarly in second last rows I want to ignore 300 value and take 200 since it relates with the year coalesce (of -) would sum all the rows value irrespective of the conditions if I may want to state

Year policy_no As_on_mar2016 As_on_Mar2015 As_on_Mar2014

2013 001 200 100

2013 002 200

2013 003 100

2014 001 200

2014 002 300 200

2014 003

2015 001 100

2015 002 300

2015 003 300

Re: Creating single variable from multiple variables

novinosrin — Tue, 11 Dec 2018 14:55:43 GMT

Goood morning, Sorry been busy, i will respond shortly

Re: Creating single variable from multiple variables

novinosrin — Tue, 11 Dec 2018 23:29:43 GMT

Hi @sameer112217 would this help?

data have;
input Year    policy_no   As_on_mar2016    As_on_Mar2015         As_on_Mar2014;
cards;
2013   001               200                  .                                             100
2013   002                 .                .                                                   200
2013   003                .                           .                                        100 
2014   001                    .                                200 .
2014   002                300                              200    .                       
2014   003              .  . .
2015   001                   100 . .
2015   002                   300 . .
2015   003                   300 . .
;

data want;
set have;
array t(*) As_on_Mar2014-As_on_mar2016;
net_amt=coalesce(of t(*));
run;

Re: Creating single variable from multiple variables

sameer112217 — Wed, 12 Dec 2018 12:40:32 GMT

Thanks