topic Re: How to insert a missing value to a missing record ? in SAS Programming

How to insert a missing value to a missing record ?

wtien196838 — Thu, 27 Jul 2017 23:34:49 GMT

Dear SAS user community,

I extra data from oracle tables through sas software.
The data layout as follow:
quarter location   entry_code volume     (about 20 vars in total)
173      loc_1       1           150
173      loc_1       2            50
173      loc_1       3           200
173      loc_1       4           300
....................................
173      loc_2       1           200
173      loc_2       3           100
173      loc-2       4            50
.....................................
173      Nation              sum all volumes

There are 80 locations and one nation total line. For each quarter, about 800 observations.
Sometime, a few locations are missing one or two entry_code.
How can I add the missing observation as
173      loc_2       2            .

It will take a long time to visual check the missing observation.
Sas user supports have any solution for that.
Thanks in advance for your help.

Regards,

WT196838

Re: How to insert a missing value to a missing record ?

ChrisBrooks — Thu, 27 Jul 2017 23:43:54 GMT

Is the variable character or numeric?

If it's numeric I would expect a missing value to automatically be inserted during the importation; if it's character I would expect it to automatically be an empty string.

Re: How to insert a missing value to a missing record ?

novinosrin — Thu, 27 Jul 2017 23:51:23 GMT

What would be the value of the volume for those missing entry codes in the sequence?

Re: How to insert a missing value to a missing record ?

Reeza — Fri, 28 Jul 2017 00:11:32 GMT

That looks like a summary report already. Are you processing anything on the SAS side from what you've shown?

There are typically several ways to add missing values into the data. Remember, computers are stupid. If it's not there they can't know it exists unless you tell them somehow. So how do we specify this?

1. List it out explicitly, this means itemizing the categories/levels you need. This can be done via a format or CLASS DATA. You can search using PRELOADFMT or CLASSDATA to see examples of this.

2. *IF* all the values are there but not necessarily in the combinations you want you can use the SPARSE option in PROC FREQ.

3. If the data is a time component you can use the PROC TIMESERIES to fill in missing data.

Re: How to insert a missing value to a missing record ?

Shmuel — Fri, 28 Jul 2017 01:09:05 GMT

Do you have a list or table of the all 80 locations ?

Do you expect to have the 4 entry_codes for all locations?

Assuming you deal now with one quarter only then do:

data help;
   set locations;
       retain quarter 173;
       do entry_code = 1 to 4; output; end;
run;

data want;
  merge help have;
  by quarter location entry_code;
run;

Re: How to insert a missing value to a missing record ?

wtien196838 — Fri, 28 Jul 2017 13:36:49 GMT

Re: How to insert a missing value to a missing record ?

wtien196838 — Fri, 28 Jul 2017 13:43:37 GMT

Thanks for all solutions. I explain a little more.
Volume column is numeric. I need to display each location within all entry codes in a report.
Currently, the report still shows the missing record BUT volume is from previous record, in this example, volume = 200 instead of volume = . (missing). I can not visual check the data since it is up to date report (now is 3 quarters).

Re: How to insert a missing value to a missing record ?

Reeza — Fri, 28 Jul 2017 15:12:40 GMT

@wtien196838 wrote:
Thanks for all solutions. I explain a little more.
Volume column is numeric. I need to display each location within all entry codes in a report.
Currently, the report still shows the missing record BUT volume is from previous record, in this example, volume = 200 instead of volume = . (missing). I can not visual check the data since it is up to date report (now is 3 quarters).

You need to explain this a lot more. Post your original data, code to generate the report and what is wrong with it. Otherwise right now we're guessing and going in circles.

Re: How to insert a missing value to a missing record ?

wtien196838 — Fri, 28 Jul 2017 23:36:00 GMT

I attached test data and the code as follow:

%let Q = 3 ; /* quarter 3 */

%macro ytd_rollup(infile);

proc sql ;
create table ytd_temp as
select loc_id, loc_desc, 100*(var0/volume) as percent format=5.1, volume, qtr
from &infile.
group by loc_id, loc_desc, qtr
union
select loc_id, loc_desc, 100*(sum(var0)/sum(volume)) as percent format 5.1, sum(volume) as volume , 999 as qtr
from &infile.
group by loc_id, loc_desc
order by loc_id, loc_desc, qtr
;
quit;

proc sql ;
create table middle_data as
select a.*, b.order
from ytd_temp a
left join order b
on a.loc_desc = b.loc_desc ;
quit;

proc sort data=middle_data;
by order qtr ;
run;

%IF &Q. EQ 2 %THEN %let var_list = Q2_per Q2_vol ;
%ELSE %IF &Q. EQ 3 %THEN %let var_list = Q2_per Q2_vol Q3_per Q3_vol ;
%ELSE %IF &Q. EQ 4 %THEN %let var_list = Q2_per Q2_vol Q3_per Q3_vol Q4_per Q4_vol ;
%ELSE %let var_list = ;

Data final_data (keep= order loc_desc YTD_per YTD_vol Q1_per Q1_vol &var_list.) ;
set middle_data;
by order qtr;
retain YTD_per YTD_vol Q1_per Q1_vol &var_list. ;

if volume = 0 then volume = . ;
if percent = 0 then percent = . ;
select (qtr);
when (171) do; Q1_per = percent; Q1_vol = volume; end;
when (172) do; Q2_per = percent; Q2_vol = volume; end;
when (173) do; Q3_per = percent; Q3_vol = volume; end;
when (174) do; Q4_per = percent; Q4_vol = volume; end;
when (999) do; YTD_per = percent; YTD_vol=volume; end;
otherwise ;
end;
if last.order then output;
run;

%mend ytd_rollup;

%ytd_rollup(initial_data)

Re: How to insert a missing value to a missing record ?

wtien196838 — Wed, 02 Aug 2017 15:46:47 GMT

Thanks for all support solutions.

Based on your suggestions, I resolved my problem.

I appreciate your support.

Best Regards,

WT196838