topic Re: an inquiry in SAS Procedures

an inquiry

Radwan — Sat, 19 Jan 2019 09:51:33 GMT

Hello everyone

i have a huge data for 15 years and for more than 2500 firms. so far it is fine, but when i started analyzing the data i found out that some companies do not have complete data for 15 years. so i tried to use If statement as follows.

data cashflow; set Radwan; 
if year= 2000 2001 2002 .... ; run;

unfortunately it does not work except when i use only one year as follows: so please help me through giving me the right code to remove the firms that do not have the data for my research period.

data cashflow; set Radwan; 
if year= 2000;  run;

Re: an inquiry

PeterClemmensen — Sat, 19 Jan 2019 10:20:55 GMT

How is your data structured? Please provide an example of what your data looks like.

Is the research period always from 2000 to 2015 or can it be any 15 consecutive years?

Re: an inquiry

Astounding — Sat, 19 Jan 2019 14:10:52 GMT

This is not the simplest problem, since SAS processes a single observation at a time. It sounds like you need a tool to examine a set of observations (all the observations for a company) to see what data is there. Does that sound right?

Have you examined your data to determine whether it might contain two observations for the same company/year combination?

What would you like the result to be ... throw out companies with missing values? Fill in missing values with zeros?

The planning is the harder part. The programming might not be simple, but it isn't as difficult as the planning.

Re: an inquiry

VDD — Sat, 19 Jan 2019 14:43:48 GMT

try using an in condition

data cashflow; 
set Radwan; 
if year in (2000, 2001, 2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015); 
run;

Re: an inquiry

Radwan — Sat, 19 Jan 2019 14:37:50 GMT

yes, the research period is 2000 - 2015.
my data look like ( code , year, cashflow) those are the variables

Re: an inquiry

Radwan — Sat, 19 Jan 2019 14:44:52 GMT

i have checked my data, as i said some companies do not have the data of 15 years so i need to delete such companies.

Re: an inquiry

Radwan — Sat, 19 Jan 2019 14:57:30 GMT

this code is working but it does not exclude the companies that do not have the data for all years.
thanks

Re: an inquiry

Astounding — Sat, 19 Jan 2019 15:01:25 GMT

While PROC SQL can handle this in a straightforward way, my expertise is more in a DATA step:

proc sort data=have;

by company year;

run;

data want;

count=0;

do until (last.company);

set have;

where (2000 <= year <= 2015);

by company;

count + 1;

end;

do until (last.company);

set have;

where (2000 <= year <= 2015);

if count = 16 then output;

end;

drop count;

run;

The top loops counts the number of observations for a company, within the proper set of years. (Note that I allowed that a company might have data for years outside the desired range, but only counted those in the proper set of years.)

The bottom loop reads the same observations, then outputs them if all the data is there. Note that the range you specified is 16 years, not 15 years.

Re: an inquiry

Radwan — Sat, 19 Jan 2019 15:09:11 GMT

do you mean (company) as code ?
because i use code as variables for the companies

Re: an inquiry

Astounding — Sat, 19 Jan 2019 15:19:47 GMT

Yes. Use CODE instead of COMPANY.

Re: an inquiry

Radwan — Sat, 19 Jan 2019 15:21:06 GMT

i just tried this code but i got error message (proc sort data=have;

by company year;

run;

data want;

count=0;

do until (last.company);

set have;

where (2000 <= year <= 2015);

by company;

count + 1;

end;

do until (last.company);

set have;

where (2000 <= year <= 2015);

if count = 16 then output;

end;

drop count;

run;)

Re: an inquiry

Astounding — Sat, 19 Jan 2019 15:22:51 GMT

It looks like you haven't yet replaced "company" with "code" as the variable name.

Re: an inquiry

Radwan — Sat, 19 Jan 2019 15:24:13 GMT

Re: an inquiry

Astounding — Sat, 19 Jan 2019 15:29:07 GMT

This means that YEAR (in your data set) is character, not numeric. Change both WHERE statements:

where ("2000" <= year <= "2015");

Re: an inquiry

Radwan — Sat, 19 Jan 2019 15:38:13 GMT

ohhhhh yes you are right . thanks

Re: an inquiry

PeterClemmensen — Sat, 19 Jan 2019 20:21:23 GMT

Alternatively you can do this. It is not as efficient, but it ensures that every relevant year is actually present for each firm.

data have;
length code $10;
code='one';
do year=2000 to 2015;
   cashflow=rand('integer', 1000, 10000);
   output;
end;
code='two';
do year=2000 to 2013;
   cashflow=rand('integer', 1000, 10000);
   output;
end;
code='three';
do year=2000 to 2015;
   cashflow=rand('integer', 1000, 10000);
   output;
end;
run;

proc sort data=have;
   by code;
run;

proc transpose data=have out=temp prefix=year;
    by code;
    id year;
    var cashflow;
run;

data want(keep=code year cashflow);
   set temp;
   array years{*} year2000-year2015;
   if nmiss(of years[*])=0 then do i=1 to dim(years);
      cashflow=years[i];
      year=input(compress(vname(years[i]), '0123456789', 'k'), 8.);
      output;
   end;
run;