BookmarkSubscribeRSS Feed
ender_akpil
Calcite | Level 5

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!!!

3 REPLIES 3
data_null__
Jade | Level 19

"SAS Variable List"

art297
Opal | Level 21

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

NN
Quartz | Level 8 NN
Quartz | Level 8

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 628 views
  • 0 likes
  • 4 in conversation