Generating new obs in existing dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 130
Accepted Solution

Generating new obs in existing dataset

I have a dataset as attached. I would like to add year 1991, 1992 and 1993 to the current dataset.

Each year of (1991, 1992, 1993) will have 6 observations (so the number of observations for each newly added year is the same as that of other years in the current dataset).

Column2, column3 and column4 are characteristic variables, for year=1991, 1992, 1993 I would like those 3 columns values are exactly the same as the rest of the dataset.

Column4 to the last column are numeric variables, I would like their values set as missing.

I was thinking

1)first to create a new dataset which have the same variables as the current dataset, then set all values(except year's value)  eqt missing;

2)Next refer to PG's code to replace Column3's value

data want;

do i = 1 by 1 until(last.datanumber);

    set have; by datanumber;

    type = choosec(i, "PARMS","STDERR","T","PVALUE","L95B","U95B");

    output;

    end;

drop i;

run;

3)Finally merge old dataset with the new dataset.

Would you please suggest a better way to do this? thank you as always.

asking question pic.png

Attachment

Accepted Solutions
Solution
‎08-19-2014 08:14 AM
Respected Advisor
Posts: 3,799

Re: Generating new obs in existing dataset

Some creative SETting might be all you need.  When using a name prefix list as X: be sure you don't have any other variables that match the mask.  You might want something more obscure but X is sufficient here.

data frame;
   set
      datahave(
obs=0)
      datahave(
in=x1 keep=_model_--_depvar_ obs=6)
      datahave(
in=x2 keep=_model_--_depvar_ obs=6)
      datahave(
in=x3 keep=_model_--_depvar_ obs=6)
      datahave
      ;
   _n_ = indexc(cats(of xSmiley Happy,'1');
   if _n_ then year = choosen(_n_,1991,1992,1993);
   run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

Re: Generating new obs in existing dataset

data have;
   set datahave(where=(year=1994));
   do year = 1991 to 1993;
      output;
   end;
run;

proc sort data=have;
by year;
run;

proc contents data=have out=num(where=(type=1));
run;

proc sql;
select name into :var separated by ' ' from num
where name ne 'year';
quit;

data want;
set have datahave;
array col &var;
   if year<1994 then do over col;
   col=.;
  end;
run;

proc print data=want;
run;

Frequent Contributor
Posts: 130

Re: Generating new obs in existing dataset

Many thanks! the code works great! I marked this as helpful since I can choose only one as correct answer.

Super User
Super User
Posts: 7,955

Re: Generating new obs in existing dataset

Well, my preferred method on these type of things is to create a template dataset and merge it:

data template;

     attrib year model type format=$20.;

     do I=1991 to 1994;

          do s="PARMS","STD","T","P-VALUE"; /* Add your other ones */   /* You could also use arrays if more complicated */

               year=put(I,4.);

               model="MODEL 1";   /* Note you could put this in a loop also if you have more than 1 */

               type=s;

               output;

          end;

     end;

run;

proc sql;

     select     COALESCE(A.YEAR,B.YEAR) as YEAR,

                    COALESCE(A.MODEL,B.MODEL) as MODEL,

                    COALESCE(A.TYPE,B.TYPE) as TYPE,

                    A.*

     from       YOUR_DATESET A

     full join   TEMP B                         /* This is key, the full join will expand the data to full of both based on on statement */

     on          A.YEAR=B.YEAR

     and        A.MODEL=B.MODEL

     and        A.TYPE=B.TYPE;

quit;

Frequent Contributor
Posts: 130

Re: Generating new obs in existing dataset

Thank you, RW9!

I run the code, and have the error message in log:

ERROR: Expression using equals (=) has components that are of different data types.

ERROR: The COALESCE function requires its arguments to be of the same data type.

Then I compare the datahave and the template just generated by data step, I found that 'year' is character in Template, while 'year' in datahave is numeric. so I reassign year in Template with numeric format, then  no more error in log.

attrib year format=4. _MODEL_ _TYPE_ format=$20.;

Solution
‎08-19-2014 08:14 AM
Respected Advisor
Posts: 3,799

Re: Generating new obs in existing dataset

Some creative SETting might be all you need.  When using a name prefix list as X: be sure you don't have any other variables that match the mask.  You might want something more obscure but X is sufficient here.

data frame;
   set
      datahave(
obs=0)
      datahave(
in=x1 keep=_model_--_depvar_ obs=6)
      datahave(
in=x2 keep=_model_--_depvar_ obs=6)
      datahave(
in=x3 keep=_model_--_depvar_ obs=6)
      datahave
      ;
   _n_ = indexc(cats(of xSmiley Happy,'1');
   if _n_ then year = choosen(_n_,1991,1992,1993);
   run;
Frequent Contributor
Posts: 130

Re: Generating new obs in existing dataset

Posted in reply to data_null__

Thank you! that is elegant!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 327 views
  • 6 likes
  • 4 in conversation