Help using Base SAS procedures

help on generating a dataset

Accepted Solution Solved
Reply
Occasional Contributor gxu
Occasional Contributor
Posts: 18
Accepted Solution

help on generating a dataset

Hi All,

I have a data set name A which contains the following columns:  ID, F1C1, F1C2, F1C3, F2C1, F2C2,F2C3 

ID    F1C1 F1C2  F1C3 F2C1 F2C2 F2C3

1     1.1    1.2     1.3    2.1     2.2   2.3

2     1.15  1.25   1.35   2.15   2.25 2.35

3     2.15  2.25    2.35  3       4       5

I want to create a new set B which contains ID,  y which is the FxCx value, and along with the levels of F and C(that's the number followng F and C in the column name in A),  there should be totally 3*2*3=18 records in my new dataset, that's

ID     Y      F C

101    1.1  1  1

101   1.2   1  2

101   1.3   1  3

101   2.1   2  1

101   2.2   2  2

101   2.3   2  3

201  1.15  1  1

201  1.25  1  2

.....

301  3    2   1

301  4   2    2

301  5   2  3

I tried using macro to do it, but failed

%macro change;

data B;

set A;

%do k=1 %to 3;

     %do F=1 %to 2;

         %do C=1 to % 3; 

               y=F&FC&C;

               output;

%end;

%end;

%end;

%mend;

%change;

It seems there are a few problems with the above code.

I cann't get F1C1, F1C2 by using F&FC&C,  I can't output the F,C either. 

Also,  I guess there should be solutions without using macro.

I will appreciate any kind of help. 

Thanks!

Ning


Accepted Solutions
Solution
‎10-31-2011 05:17 PM
Occasional Contributor gxu
Occasional Contributor
Posts: 18

help on generating a dataset

Tom,

This works,   thank you a lot!

Best regards,

Ning

View solution in original post


All Replies
PROC Star
Posts: 7,468

help on generating a dataset

Ning,

I'm not sure what your rules are for changing the IDs but, for the other variables, I think that the following does what you want:

data have;

  input ID    F1C1 F1C2  F1C3 F2C1 F2C2 F2C3;

  cards;

1     1.1    1.2     1.3    2.1     2.2   2.3

2     1.15  1.25   1.35   2.15   2.25 2.35

3     2.15  2.25    2.35  3       4       5

;

data want(drop=f1c1--f2c3);

  set have;

  array x(*) f:;

  do f=1 to 2;

    do c=1 to 3;

      y=x(c*f);

      output;

          end;

  end;

run;

Occasional Contributor gxu
Occasional Contributor
Posts: 18

help on generating a dataset

Hi Art,

Thanks, I don't have specific rules for changing IDs.  The do loop %do k=1 %to 3; %end in my code should be removed.

I ran your code and got the following result:

                                        Obs    ID    f    c      y

                                       1     1    1    1    1.10

                                       2     1    1    2    1.20

                                       3     1    1    3    1.30

                                       4     1    2    1    1.20

                                       5     1    2    2    2.10

                                       6     1    2    3    2.30

                                       7     2    1    1    1.15

                                       8     2    1    2    1.25

                                       9     2    1    3    1.35

                                      10     2    2    1    1.25

                                      11     2    2    2    2.15

                                      12     2    2    3    2.35

                                      13     3    1    1    2.15

                                      14     3    1    2    2.25

                                      15     3    1    3    2.35

                                      16     3    2    1    2.25

                                      17     3    2    2    3.00

                                      18     3    2    3    5.00

It seems there is something wrong,  for example, the 4th and 5th obs.   I use SAS9.1

Can you help to check why I didn't get the desired output?

Super User
Posts: 10,023

help on generating a dataset

It is almost like Art's.

data have;
  input ID    F1C1 F1C2  F1C3 F2C1 F2C2 F2C3;
  cards;
1     1.1    1.2     1.3    2.1     2.2   2.3
2     1.15  1.25   1.35   2.15   2.25 2.35
3     2.15  2.25    2.35  3       4       5
;
run;

 

data want(drop=i f: );
  set have;
  array x{*} f:;
  do i=1 to dim(x);
    y=x{i}; 
    _F=scan(vname(x{i}),1,,'kd');_C=scan(vname(x{i}),2,,'kd');
    output;
  end;
run;

Ksharp

Occasional Contributor gxu
Occasional Contributor
Posts: 18

help on generating a dataset

Ksharp,

Thank you!

I got the error message as (the "-" is under the "," before "kd")

******************************************************************************

95       _F=scan(vname(x{i}),1,,'kd');_C=scan(vname(x{i}),2,,'kd');

                                            -                                        -

                                          159                                    159

ERROR 159-185: Null parameters for SCAN are invalid.

*******************************************************************************

I use SAS9.1, is this caused by the old version?

Super User
Super User
Posts: 7,039

Re: help on generating a dataset

Not that macro approach is right for this problem, but the main problem with your code is that you never created the dataset variables you wanted to store the values of the macro variables.

%macro change;

data B;

  set A;

%do F=1 %to 2;  %do C=1 %to 3;

  y=F&F.C&C;

  f=&f;

  c=&c;

  output;

%end; %end;

run;

%mend change;

%change;

Solution
‎10-31-2011 05:17 PM
Occasional Contributor gxu
Occasional Contributor
Posts: 18

help on generating a dataset

Tom,

This works,   thank you a lot!

Best regards,

Ning

🔒 This topic is solved and locked.

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

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