BookmarkSubscribeRSS Feed
wtien196838
Quartz | Level 8

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

9 REPLIES 9
ChrisBrooks
Ammonite | Level 13

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.

novinosrin
Tourmaline | Level 20

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

Reeza
Super User

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.

Shmuel
Garnet | Level 18

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
Quartz | Level 8
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).
Reeza
Super User

@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.

wtien196838
Quartz | Level 8

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)

wtien196838
Quartz | Level 8

Thanks for all support solutions.

Based on your suggestions, I resolved my problem. 

I appreciate your support.

Best Regards,

 

WT196838

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1231 views
  • 0 likes
  • 5 in conversation