topic Re: Create a new category for an existing variable in New SAS User

Create a new category for an existing variable

KarolineN — Fri, 02 Oct 2020 15:11:35 GMT

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

Re: Create a new category for an existing variable

ballardw — Fri, 02 Oct 2020 15:28:18 GMT

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

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.

Re: Create a new category for an existing variable

Reeza — Fri, 02 Oct 2020 16:05:47 GMT

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.

Re: Create a new category for an existing variable

KarolineN — Fri, 02 Oct 2020 16:30:37 GMT

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

Re: Create a new category for an existing variable

PaigeMiller — Fri, 02 Oct 2020 16:48:56 GMT

/* 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/

Re: Create a new category for an existing variable

Ksharp — Sat, 03 Oct 2020 11:46:38 GMT

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;