Create a new dataset

[walker105]

Hi, 

I am working on data cleaning/management, and getting stuck in some points.. 

I have dataset as follows:

id name type score1 

1  apple   A      15

1  apple   B      18 

2  pear     A      17

2  pear     B      20

I would like to create new dataset in this order:

id name typeA_score typeB_score

1 apple      15                 18 

2 pear        17                 20 

Could anyone please let me know how I create the new dataset?

Many thanks! 

[PaigeMiller]

May I suggest you not do this, and leave the data as in your first data set. Data is much easier to work with in a long data set rather than a wide data set. What is the purpose and goal of making the second data set? What will you do with the 2nd data set that you can't do with the first data set? Do you want a report that looks like this? Do you want to perform some statistical or graphical analysis on this data?

[ghosh]

I agree with @PaigeMiller it's not a good idea to reshape your actual data.  If all you want is a report the just use Proc Report.  As a bonus, the output dataset (want) will provide you with the dataset you requested, just rename the variables _C3_ and _C4_ as required.

proc report nowd out=want;
  columns id name type,score1;
  define id /group;
  define name /group;
  define type /across;
  define score1/analysis;
run;

[Tom]

Easy enough to do with PROC TRANSPOSE using the ID statement and the PREFIX= and SUFFIX= options.

data have ;
  input id name $ type $ score1 ;
cards;
1  apple   A      15
1  apple   B      18 
2  pear    A      17
2  pear    B      20
;

proc transpose data=have out=want prefix=type suffix=_score;
  by id name;
  id type;
  var score1;
run;

proc print data=want;
run;

                                typeA_    typeB_
Obs    id    name     _NAME_     score     score

 1      1    apple    score1      15        18
 2      2    pear     score1      17        20

[walker105]

Thanks for your suggestions!