Create a new category for an existing variable

[KarolineN]

Hi SAS community,

I am wondering if someone could help me with the following issue. I have a dataset that looks something like this:

Year      Company        Substance              Value

2011      Max                A                             2.3

2012      Max               B                              4.3

2013      Max               C                              6.8

2014      Max               A                               7.4

How do I create, in a datastep, one new row with the year 2010 and Company Max but where substance and value is empty? 

Thank you very much for any help!

Kind regards,

Karoline

[ballardw]

---

@KarolineN wrote:

Hi SAS community,

I am wondering if someone could help me with the following issue. I have a dataset that looks something like this:

Year      Company        Substance              Value

2011      Max                A                             2.3

2012      Max               B                              4.3

2013      Max               C                              6.8

2014      Max               A                               7.4

How do I create, in a datastep, one new row with the year 2010 and Company Max but where substance and value is empty? 

Thank you very much for any help!

Kind regards,

Karoline

---

Assuming that you want something that actually does this for multiple companies this should work.

proc sort data=have;
   by company year;
run;
data want;
   set have;
   by company;
   output;
   if last.company then do;
      year=2010;
      call missing(substance,value);
      output;
   end;
run;

proc sort data=want;
   by company year;
run;

or

proc sort data=have;
   by company year;
run;
proc sql;
   create table temp as
   select distinct(company), 2010 as year
   from have
   order by company, year
   ;
quit;

data want;
   merge have temp;
   by company year;
run;

If multiple year values need to be created that is another story.

[Reeza]

Is it a single row you need to fix or do you need to ensure you have the years 2010 to 2014 for every company?
Those are two different problems with two different solutions.

[KarolineN]

Hi, 

You are right. I realize I simplified it too much. I have data for several companies and I need to ensure I have the year 2010 - 2014 for all. But I do not want duplicates. That is, if a company already has data for 2010, I do not also want an empty row for to 2010 in addition. 

Thank you very much for your help!

Kind regards,

Karoline

[PaigeMiller]

/* UNTESTED CODE */
proc sql;
    create table years as select distinct year from have;
    create table company as select distinct company from have;
    /* Cartesian join */
    create table all as select y.year,c.company from years as y,company as c; 
    create table want as select coalesce(h.year,a.year) as year,
        coalesce(h.company,a.company) as company,h.substance,h.value
        from have as h full join all as a by h.year=a.year and h.company=a.company
    order by calculated company,calculated year;
quit;

This is untested. If you want it tested, please supply a meaningful example data set following these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

[Ksharp]

data have;
input Year      Company    $    Substance     $         Value;
cards;
2011      Max                A                             2.3
2012      Max               B                              4.3
2013      Max               C                              6.8
2014      Max               A                               7.4
;

data want;
 if _n_=1 then do;
   if 0 then set have;
   year=2010; company='Max';output;
 end;
 set have;
 output;
run;