DATA Step, Macro, Functions and more

create new rows based on existing rows.

Reply
Contributor brm
Contributor
Posts: 30

create new rows based on existing rows.

For each record in test(dataset) we have to create five records in new dataset(final)

ex:in test(dataset) 1st record

Account            Milestone Period  
SDI                   2011Q2

Based on test(dataset) we have to create another dataset Final(dataset) with 5 records.

Account              Milestone Period      Measurement

SDI                    2011 Q2                 Approval Rate
SDI                    2011 Q2            Days to Approval
SDI                    2011 Q2                 Fulfillment Rate
SDI                    2011 Q2                 Days to Ship
SDI                    2011 Q2                 Compliance

We have to repeat this all records in dataset(test).Can someone suggest me how to do this?

Thanks,

sk

SAS Employee
Posts: 104

create new rows based on existing rows.

  /* Create the original dataset for testing */

Data original;

input Account:$3. Milestone Period:$2.;

datalines;

SDI                   2011 Q2

run;

  /* Create a simple dataset with the 5 unique measurement observations */

Data Measures;

infile datalines truncover;

input Measurement $ 1-25;

datalines;

Approval Rate

Days to Approval

Fulfillment Rate

Days to Ship

Compliance

;

run;

/* Do the actual work */

/* A cartesian join is perfect for this */

proc sql;

create table final as

select *

   from original, measures

;

quit;

Frequent Contributor
Posts: 81

create new rows based on existing rows.

data test;

          length Account Milestone_Period $20;

          account = 'SDI';

          milestone_period = '2011 Q2';

          output;

          account = 'SDII';

          milestone_period = '2011 Q3';

          output;

run;

data final;

          length Measurement $50;

          set test;

          array test {5} $50 _temporary_ ('Approval Rate','Days to Approval','Fulfillment Rate','Days to Ship','Compliance');

          do i = 1 to 5;

                    measurement = test(i);

                    output;

          end;

          drop i;

run;

proc print; var account milestone_period measurement; run;

Ask a Question
Discussion stats
  • 2 replies
  • 1325 views
  • 1 like
  • 3 in conversation