07-27-2017 07:34 PM
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.
07-27-2017 07:43 PM
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.
07-27-2017 08:11 PM
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.
07-27-2017 09:09 PM
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;
07-28-2017 09:43 AM
07-28-2017 11:12 AM
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.
07-28-2017 07:36 PM
I attached test data and the code as follow:
%let Q = 3 ; /* quarter 3 */
proc sql ;
create table ytd_temp as
select loc_id, loc_desc, 100*(var0/volume) as percent format=5.1, volume, qtr
group by loc_id, loc_desc, qtr
select loc_id, loc_desc, 100*(sum(var0)/sum(volume)) as percent format 5.1, sum(volume) as volume , 999 as qtr
group by loc_id, loc_desc
order by loc_id, loc_desc, qtr
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 ;
proc sort data=middle_data;
by order qtr ;
%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.) ;
by order qtr;
retain YTD_per YTD_vol Q1_per Q1_vol &var_list. ;
if volume = 0 then volume = . ;
if percent = 0 then percent = . ;
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;
if last.order then output;
08-02-2017 11:46 AM
Thanks for all support solutions.
Based on your suggestions, I resolved my problem.
I appreciate your support.