DATA Step, Macro, Functions and more

Dynamically assign column name

Reply
Occasional Contributor
Posts: 11

Dynamically assign column name

Hi everybody,

I have a data that i want to transform it. Here is the original data and the data that i want.

data target;
id=1;
year=2007;
Jan=10; Feb=11; March=12; Apr=10; May=8; June=6;
July=7; Agust=8; Sep=9; Oct=10; Nov=11; Dec=12 ;
output;
id=1;
year=2008;
Jan=11; Feb=12; March=13; Apr=11; May=9; June=7;
July=8; Agust=9; Sep=10; Oct=11; Nov=12; Dec=13 ;
output;

run;

  Obs    id    year    Jan    Feb    March    Apr    May    June    July    Agust    Sep   Oct    Nov    Dec

            1      1    2007     10     11      12      10     8       6       7       8           9        10     11     12
            2      1    2008     11     12      13      11     9       7       8       9          10        11     12     13

i want this data in that form:

                                                      Obs    year    goal

                                                         1    2007     10
                                                         2    2007     11
                                                         3    2007     12
                                                         4    2007     10
                                                         5    2007      8
                                                         6    2007      6
                                                         7    2007      7
                                                         8    2007      8
                                                         9    2007      9
                                                        10    2007     10
                                                        11    2007     11
                                                        12    2007     12
                                                        13    2008     11
                                                        14    2008     12
                                                        15    2008     13
                                                        16    2008     11
                                                        17    2008      9
                                                        18    2008      7
                                                        19    2008      8
                                                        20    2008      9
                                                        21    2008     10
                                                        22    2008     11
                                                        23    2008     12
                                                        24    2008     13

I do it  this way:

data test(keep=year goal);

array v_month{12} Jan Feb March Apr May June July Agust Sep Oct Nov Dec  ;

set target;

do c=1 to 12;

    yil=id;

    goal=v_month{c};

    output;

end;

run;

i wonder is there any other way, since in the orginal source table sometimes more than 100 columns!!!

Respected Advisor
Posts: 3,777

Dynamically assign column name

"SAS Variable List"

PROC Star
Posts: 7,356

Dynamically assign column name

You have to provide more info.  Are you only interested in the year and the 12 amounts that are in variables jan--dec?

Regular Contributor
Regular Contributor
Posts: 166

Dynamically assign column name

Hi,

What i usually use is .

PROC TRANSPOSE DATA=target OUT=WANT;

BY ID YEAR;

RUN;

Please check if it helps.. But you do need to keep in mind your data size and the processing time.

Ask a Question
Discussion stats
  • 3 replies
  • 116 views
  • 0 likes
  • 4 in conversation