topic Re: Fill missing values based on a common ID in SAS Programming

Fill missing values based on a common ID

tyq1992 — Mon, 10 Jun 2019 07:32:42 GMT

Hi all,

Am fairly new to SAS and am encountering the following problem. Appreciate if someone could shed some light on it! 🙂

I have a dataset which looks like this:

SH_NAME refers to the shareholders who own the firm while holding refers to the percentage ownership of the firm.

REVENUE, PROFIT, ASSETS & EQUITY figures are that of the firm total.

There are 20 other columns with such firm-level variables. For some variable, there is no info on it (e.g. Equity in the example below).

FIRM_NAME	SH_NAME	HOLDING	REVENUE	PROFIT	ASSETS	EQUITY
A	A1	100	10	2	20	10
B	B1	17
B	B2	17	100	40	50
B	B3	17
B	B4	17
B	B5	17
B	B6	17

Is there a way to fill in the missing blanks such that the final result will look like the following table?

FIRM_NAME	SH_NAME	HOLDING	REVENUE	PROFIT	ASSETS	EQUITY
A	A1	100	10	2	20	10
B	B1	17	100	40	50
B	B2	17	100	40	50
B	B3	17	100	40	50
B	B4	17	100	40	50
B	B5	17	100	40	50
B	B6	17	100	40	50

Thanks in advance!

Re: Fill missing values based on a common ID

PeterClemmensen — Mon, 10 Jun 2019 07:46:29 GMT

Here is one way

data have;
infile datalines missover dsd dlm=',';
input FIRM_NAME $ SH_NAME $ HOLDING REVENUE PROFIT ASSETS EQUITY;
datalines;
A,A1,100,10,2,20,10
B,B1,17,
B,B2,17,100,40,50,,
B,B3,17,
B,B4,17,
B,B5,17,
B,B6,17,
;

data temp;
   update have(obs=0) have;
   by FIRM_NAME;
   output;
run;

proc sort data=temp;
   by FIRM_NAME descending SH_NAME; 
run;

data temp2;
   update temp(obs=0) temp;
   by FIRM_NAME;
   output;
run;

proc sort data=temp2 out=want;
   by FIRM_NAME SH_NAME; 
run;

proc datasets lib=work nolist;
   delete temp:;
run;

Re: Fill missing values based on a common ID

tyq1992 — Wed, 12 Jun 2019 06:30:32 GMT

Thanks for the suggestion!

However, as i have c.20,000 rows (and around 10,000 blanks that needs to be filled based on the firm ID), writing out each line may take up quite some time, in which case Excel may be a better option to edit the cells?

Re: Fill missing values based on a common ID

PeterClemmensen — Wed, 12 Jun 2019 06:55:29 GMT

I just took your sample data as an example here. My code can handle your 20.000 rows easily 🙂

Re: Fill missing values based on a common ID

Patrick — Wed, 12 Jun 2019 10:40:06 GMT

Based on your sample data below could work.

data have;
infile datalines missover dsd dlm=',';
input FIRM_NAME $ SH_NAME $ HOLDING REVENUE PROFIT ASSETS EQUITY;
datalines;
A,A1,100,10,2,20,10
B,B1,17,
B,B2,17,100,40,50,,
B,B3,17,
B,B4,17,
B,B5,17,
B,B6,17,
;
proc sort data=have;
  by firm_name sh_name;
run;

%let firm_level_vars=REVENUE PROFIT ASSETS;

%let firm_level_vars2=%sysfunc(compbl(&firm_level_vars));
%let firm_level_vars2=%sysfunc(translate(&firm_level_vars2,%str(,),%str( )));
data want;
  merge 
    have(drop=&firm_level_vars) 
    have(keep=firm_name &firm_level_vars 
         where=(n(&firm_level_vars2)>0)
        )
    ;
  by firm_name;
run;

proc print data=want;
run;

Re: Fill missing values based on a common ID

tyq1992 — Fri, 14 Jun 2019 02:08:03 GMT

Thanks! This worked fine for me! 🙂