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
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.
What would be the value of the volume for those missing entry codes in the sequence?
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.
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;
@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.
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)
Thanks for all support solutions.
Based on your suggestions, I resolved my problem.
I appreciate your support.
Best Regards,
WT196838
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.