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

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

1 ACCEPTED SOLUTION

Accepted Solutions
gxu
Calcite | Level 5 gxu
Calcite | Level 5

Tom,

This works,   thank you a lot!

Best regards,

Ning

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

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;

gxu
Calcite | Level 5 gxu
Calcite | Level 5

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?

Ksharp
Super User

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

gxu
Calcite | Level 5 gxu
Calcite | Level 5

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?

Tom
Super User Tom
Super User

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;

gxu
Calcite | Level 5 gxu
Calcite | Level 5

Tom,

This works,   thank you a lot!

Best regards,

Ning

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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