topic help on generating a dataset in SAS Procedures

help on generating a dataset

gxu — Mon, 31 Oct 2011 02:24:22 GMT

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;

%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

help on generating a dataset

art297 — Mon, 31 Oct 2011 02:47:58 GMT

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;

run;

help on generating a dataset

Ksharp — Mon, 31 Oct 2011 07:13:39 GMT

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

help on generating a dataset

gxu — Mon, 31 Oct 2011 21:06:15 GMT

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?

Re: help on generating a dataset

Tom — Mon, 31 Oct 2011 21:12:26 GMT

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;

help on generating a dataset

gxu — Mon, 31 Oct 2011 21:15:13 GMT

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?

help on generating a dataset

gxu — Mon, 31 Oct 2011 21:16:59 GMT

Tom,

This works, thank you a lot!

Best regards,

Ning