SAS Support Communities

Dear SAS users,    I have a data set similar to the one below. I want to calculate "ratio" based values specified on the 'type' variable without hard coding the type values. since the type could be different month to month.  For example: for row 1: ratio = (_1a_val * 100)/360; row 3: ratio = (_1b_val * 100)/360;     data have; input class $ type $ month _1a_val _1b_val _1c_val; datalines; Plas-1 1a1 0 2.5 3.5 4.5 Plas-1 1a1 1 2.5 3.5 4.5 Plas-1 1b1 0 2.5 3.5 4.5 Plas-1 1b1 1 2.5 3.5 4.5 Plas-1 1c1 0 2.5 3.5 4.5 Plas-1 1c1 1 2.5 3.5 4.5 Plas-1 1c1 2 2.5 3.5 4.5 blod-2 1a1 0 1.5 2.5 3.5 blod-2 1a1 1 1.5 2.5 3.5 blod-2 1b1 0 1.5 2.5 3.5 blod-2 1b1 1 1.5 2.5 3.5 blod-2 1b1 2 1.5 2.5 3.5 blod-2 1c1 0 1.5 2.5 3.5 blod-2 1c1 1 1.5 2.5 3.5 blod-2 1c1 2 1.5 2.5 3.5 ; run;     Here is the result. I want   class type month _1a_val _1b_val _1c_val ratio Plas-1 1a1 0 2.5 3.5 4.5 0.6944444444 Plas-1 1a1 1 2.5 3.5 4.5 0.6944444444 Plas-1 1b1 0 2.5 3.5 4.5 0.9722222222 Plas-1 1b1 1 2.5 3.5 4.5 0.9722222222 Plas-1 1c1 0 2.5 3.5 4.5 1.25 Plas-1 1c1 1 2.5 3.5 4.5 1.25 Plas-1 1c1 2 2.5 3.5 4.5 1.25 blod-2 1a1 0 1.5 2.5 3.5 0.4166666667 blod-2 1a1 1 1.5 2.5 3.5 0.4166666667 blod-2 1b1 0 1.5 2.5 3.5 0.6944444444 blod-2 1b1 1 1.5 2.5 3.5 0.6944444444 blod-2 1b1 2 1.5 2.5 3.5 0.6944444444 blod-2 1c1 0 1.5 2.5 3.5 0.9722222222 blod-2 1c1 1 1.5 2.5 3.5 0.9722222222 blod-2 1c1 2 1.5 2.5 3.5 0.9722222222   ... View more

Deal SAS Users,  I would like to change the order of rows based on the values provided in the "order" column. for example. The order variable only contains the letter the first letter. however, the numbers will be sorted in descending order.  For example:  data have; input type $ desc $ mo order $10.; datalines; A D2 0 B,D A D1 1 B,D A B1 2 B,D B D1 0 D,B B B0 1 D,B B D2 2 D,B B B1 3 D,B C D2 0 B,D C D1 1 B,D C B1 2 B,D D X2 0 Z,Y,X D Y1 1 Z,Y,X D Z1 2 Z,Y,X ; run; data want; input type $ desc $ mo order $ 7-12 new_desc $ 13 - 15; datalines; A D2 2 B,D B1 A D1 0 B,D D2 A B1 1 B,D D1 B D1 2 D,B D2 B B0 0 D,B D1 B D2 3 D,B B1 B B1 1 D,B B0 C D2 2 B,D B1 C D1 0 B,D D2 C B1 1 B,D D1 D X2 2 Z,Y,X Z1 D Y1 1 Z,Y,X Y1 D Z1 0 Z,Y,X X2 ; run;     ... View more

I have a dataset similar to this. I want to compare fcst_val and val variables and retain the instance of desc column where the fcst_val less than or equal to the val.  Here is an example:  data have; input type $ desc $ mo fcst_val val; datalines; A D2 0 100 1000 A D1 1 100 1050 A B1 2 100 1100 B D1 0 200 150 B B0 1 200 250 B D2 2 200 300 B B1 3 200 350 c D2 0 400 10 C D1 1 400 300 C B1 1 400 400 ; run; data want; input type $ desc $ mo fcst_val val new_val; datalines; A D2 0 100 1000 D2 A D1 1 100 1050 D2 A B1 2 100 1100 D2 B D1 0 200 150 B0 B B0 1 200 250 B0 B D2 2 200 300 B0 B B1 3 200 350 B0 c D2 0 400 10 B1 C D1 1 400 300 B1 C B1 1 400 500 B1 ; run;     ... View more

Hi SAS Users,    I have a global macro which contains a list of values in which the order they appear represent the order they should appear in the dataset.    %let values = A, C, D, Z;  I want to loop through the list and create a dataset similar to this. I want this to be a dynamic dataset that updates itself when a new list is added.    Values order  A  1 C 2 D 3 Z 4 ... View more

Hello everyone, I have an aggregated dataset that I want to expand for further calculation. Does anyone know I could achieve this in data step programming.   Dataset have     data have; input name $ group $ mo_freq $ value ; datalines; wlf_01 actv 1-4 0 wlf_01 actv 5-7 1.15 wlf_01 actv 8-10 1.00 wlf_02 dflt 1-4 0 wlf_02 dflt 5-6 5.15 wlf_02 dflt 7-8 4.25 wlf_02 dflt 9-10 0 wlf_03 prog 1-10 2.23 ; run;     dataset want   name group mo_freq month value wlf_01 actv 1-4 1 0.00 wlf_01 actv 1-4 2 0.00 wlf_01 actv 1-4 3 0.00 wlf_01 actv 1-4 4 0.00 wlf_01 actv 5-7 5 1.15 wlf_01 actv 5-7 6 1.15 wlf_01 actv 5-7 7 1.15 wlf_01 actv 8-10 8 1.00 wlf_01 actv 8-10 9 1.00 wlf_01 actv 8-10 10 1.00 wlf_02 dflt 1-4 1 0.00 wlf_02 dflt 1-4 2 0.00 wlf_02 dflt 1-4 3 0.00 wlf_02 dflt 1-4 4 0.00 wlf_02 dflt 5-6 5 5.15 wlf_02 dflt 5-6 6 5.15 wlf_02 dflt 7-8 7 4.25 wlf_02 dflt 7-8 8 4.25 wlf_02 dflt 9-10 9 0.00 wlf_02 dflt 9-10 10 0.00 wlf_03 prog 1-10 1 2.23 wlf_03 prog 1-10 2 2.23 wlf_03 prog 1-10 3 2.23 wlf_03 prog 1-10 4 2.23 wlf_03 prog 1-10 5 2.23 wlf_03 prog 1-10 6 2.23 wlf_03 prog 1-10 7 2.23 wlf_03 prog 1-10 8 2.23 wlf_03 prog 1-10 9 2.23 wlf_03 prog 1-10 10 2.23     ... View more

I would like to merge the below two datasets with id, type, type2 and code and If I don’t find a match I would like to take the value from the second dataset were the keys are missing and assign to the first datasets.   Here is the desired output.   Dataset-1 data t3; input id code $ type $ type2 $ price; datalines; 101 AAA L80 GT80 50.0 101 AAB L60 LT20 20.2 101 AAC L20 LE60 30.5 101 ABA L80 EQ20 40.2 102 AAA L80 GT80 50.2 102 AAB L60 LT20 51.0 102 AAC L20 LE60 30.2 102 ABA L80 GT20 20.5 ; run;   dataset-2 data t4; input id code $ type $ type2 $ new_price; datalines; 101 .   .   .    25.5 101 AAA L80 GT80 22.5 101 AAA L20 GT80 1.0 102 .   .   .    32.5 102 AAC L20 LE60 18.5 102 AAC L20 LE60 12.0 102 ACC L80 GT30 11.0 ; run;   Desired output;   id code type type2 price new_price 101 AAA L80 GT80 50 22.5 101 AAB L60 LT20 20.2 25.5 101 AAC L20 LE60 30.5 25.5 101 ABA L80 EQ20 40.2 25.5 102 AAA L80 GT80 50.2 32.5 102 AAB L60 LT20 51 32.5 102 AAC L20 LE60 30.2 12 102 ABA L80 GT20 20.5 32.5 ... View more

Hi everyone,    I really appreciate all you help on this. I have racked my brain for hours to solve it using perl reg experation but no luck.    I wish to create a variable called "name" from the variable "text" from the below dataset.      data Have; ID=1; text='total16z01_model_avg_jan_2016'; output; ID=2; text='total17z02_model_1_avg_feb_2017'; output; ID=3; text='total13b1_model_2_avg_2013'; output; run; desired output:      ID text name 1 total16z01_model_avg_jan_2016 total16z01 2 total17z02_model_1_avg_feb_2017 total17z02_1 3 total13b1_model_2_avg_2013 total13b1_2   Thanks for you help    ... View more

Hi SASUSERs,   I needed to lag the column variable based on the number of month different between start and end date. Below is the input data set and desired output.   I appreciate your input.     input dataset:  Type st_dt id ed_dt amnt AA 12/1/2016 0 1/1/2017 100.00 AA 1/1/2017 1 1/1/2017 200.00 AA 2/1/2017 2 1/1/2017 300.00 AA 3/1/2017 3 1/1/2017 10.00 BB 12/1/2016 0 2/1/2017 50.00 BB 1/1/2017 1 2/1/2017 105.00 BB 2/1/2017 2 2/1/2017 210.00 BB 3/1/2017 3 2/1/2017 140.00 CC 12/1/2016 0 3/1/2017 130.00 CC 1/1/2017 1 3/1/2017 124.00 CC 2/1/2017 2 3/1/2017 242.00 CC 3/1/2017 3 3/1/2017 298.00     desired output;  Type st_dt id ed_dt amnt AA 12/1/2016 0 1/1/2017   AA 1/1/2017 1 1/1/2017 100.00 AA 2/1/2017 2 1/1/2017 200.00 AA 3/1/2017 3 1/1/2017 300.00 BB 12/1/2016 0 2/1/2017   BB 1/1/2017 1 2/1/2017   BB 2/1/2017 2 2/1/2017 50.00 BB 3/1/2017 3 2/1/2017 105.00 CC 12/1/2016 0 3/1/2017   CC 1/1/2017 1 3/1/2017   CC 2/1/2017 2 3/1/2017   CC 3/1/2017 3 3/1/2017 130.00 Here is what I started with but it is not working as I expected it.   %macro xyz();   data t2; set inputdsn; by type act_dte; %do lag_value= 1 %to 70; if not first.type and first.st_dt then do;      if st_dt ne lag&lag_value(st_dt) then      lag_amnt = lag&lag_value(amnt); end; %end; run; %mend;   %xyz(); ... View more