BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

Hi,

suppose I have the following table:

CompanyYearCond1Cond2
A20062344
A20082344
B200712
B200934

 

In the table above, there are some gaps: some years are missing for each company. But for company A, the conditions for both years 2006 and 2008 are the same, so what I would like to do is to add a new row for the company A for the year 2007 with the same conditions, but for company B remain as is because the conditions are different (so don't add year 2008 for B).

The new table should be like this:

CompanyYearCond1Cond2
A20062344
A20072344
A20082344
B200712
B200834

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Daniel,

Here is a slightly different approach that seems to work on both of your example datasets:

%let factor=1;

data need;

  set have end=eof;

  by company cond1 cond2 notsorted;

  if not eof then set have(firstobs=2 keep=year cond1 cond2

                           rename=(cond1=nextcond1

                                   cond2=nextcond2

                                   year=nextyear)); 

  if last.company then do;

    flag=4;

    output;

  end;

  else if cond1 eq nextcond1 and cond2 eq nextcond2 then do;

    do year=year to nextyear-1;

      flag=1;

      output;

    end;

  end;

  else if year+&factor lt nextyear then do;

    do year=year to year+&factor;

      flag=2;

      output;

    end;

  end;

   else do;

    flag=3;

    output;

  end;

run;

View solution in original post

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Can you clarify your test data as in the first table you have 2007 and 2009 for B, but in the second you have 2007 and 2008.

The easiest way I see of doing this is to create a dataset with distinct Company, min(year), max(year), then call execute() some statements to create then output table.

art297
Opal | Level 21

Daniel,

Here is a slightly different approach that seems to work on both of your example datasets:

%let factor=1;

data need;

  set have end=eof;

  by company cond1 cond2 notsorted;

  if not eof then set have(firstobs=2 keep=year cond1 cond2

                           rename=(cond1=nextcond1

                                   cond2=nextcond2

                                   year=nextyear)); 

  if last.company then do;

    flag=4;

    output;

  end;

  else if cond1 eq nextcond1 and cond2 eq nextcond2 then do;

    do year=year to nextyear-1;

      flag=1;

      output;

    end;

  end;

  else if year+&factor lt nextyear then do;

    do year=year to year+&factor;

      flag=2;

      output;

    end;

  end;

   else do;

    flag=3;

    output;

  end;

run;

ilikesas
Barite | Level 11

Hi Arthur,

I deleted my previous reply about the code not working for my second data.

In reality it didn't work on the second data after I ran it on the first data, but when I restarted my SAS program and ran the code immediately on the second data it worked perfectly, and the same thing for data_null's code.

Maybe I had a problem because of the %let factor=1; because SAS didn't re=assign it when I ran the code on the second data right after I ran it on the first data?

And one very last thing for this discussion, could you please re-upload the code that fills the years ONLY in the case where the adjacent years (for a given company) have the same conditions?

thanks

art297
Opal | Level 21

Daniel: For future reference .. you should NEVER delete a post to which others have responded.

The main purpose of the forum is to help people learn how to use SAS, and the history of how a problem is solved (including everyone's suggested solutions) is critical to everyone's learning path.

When you delete a post, all of those responses are lost Smiley Sad

ilikesas
Barite | Level 11

sorry, just thought that it will be less messy that way...

I also edited my last reply with a small request if I may...

thanks

art297
Opal | Level 21

Post your new question by starting a new discussion. And, in doing so, provide example have and want datasets, as well as explain what you are NOW looking for. If your last question is answered, mark it as having been answered.

ilikesas
Barite | Level 11

was wandering, if I delete my message do all the replies to it also get deleted? and if so is it possible to reinstate what I deleted?

art297
Opal | Level 21

Yes, all responses get deleted. If there is a way to reinstate them, I'm not aware of it.

However, I know someone who would know:

ilikesas
Barite | Level 11

I tried to send communities admin a direct message but wasn't allowed, is there another way I can reach admin?

art297
Opal | Level 21

She already received it based on me identifying her in my last message.

Community_Help
SAS Employee

Hi there - my apologies for the late response. It's true unfortunately if you delete your message we are unable to reinstate it. I'm so sorry.

data_null__
Jade | Level 19

Maybe I understand now.  I wasn't paying attention after the second time you changed you mind.:smileyshocked:

I added observations for the years that are not filled and set COND1 and 2 to missing.  I also identified the original obs to help with checking.

data have(sortedby=company year);
   infile cards expandtabs;
  
input Company :$1. Year Cond1 Cond2;
   cards;
A  2006  23 44
A  2008  23 44
B  2007  1  2
B  2009  3  4
C  2006  23 44
C  2007  23 44
C  2008  1  2
C  2010  3  4
C  2011  23 44
C  2014  23 44
D  2006  23 44
D  2009  23 44
D  2010  23 44
D  2012  23 44
E  2005   3  6
E  2009   5  6
E  2010   5  6
F  2006 23 44
F  2009 23 44
F  2013 1  2
G  2006 10 20
G  2008 10 20
H  2006 23 44
H  2009 23 44
H  2011 23 44
H  2014 1  2
;;;;
   run;
proc print;
  
by company;
   id company;
   run;
%let factor=1;
data need;
   set have end=eof;
   by company cond1 cond2 notsorted;
  
if not eof then set have(firstobs=2 keep=year rename=year=nextyear); 
  
if last.company then do; flag=4; output; end;
  
else if not last.cond2 then do year=year to nextyear-1; /*Cond1 and Cond2 eq NEXT obs Cond1 and Cond2*/
      flag=
3;
     
output;
     
end;
  
else if last.cond2 then do; /*Does not equal next obs*/
      syear = year;
      do year=year to nextyear-1;
        
if year gt syear+&factor then call missing(of cond:); /*Fill all years but set cond 1 and 2 to missing after factor year*/
         flag=
1;
        
output;
        
end;
     
end;
  
drop nextyear syear;
   run;
data need;
   merge have(in=in1 keep=company year) need;
   by company year;
   Length Source $8;
   Source = ifc(in1,
'Original','Filled');
   run;
proc print;
  
by company;
   id company;
   run;


3-3-2015 9-01-34 AM.png3-3-2015 9-03-47 AM.png

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 1605 views
  • 3 likes
  • 5 in conversation