topic Re: Create a new dataset in SAS Programming

Create a new dataset

walker105 — Mon, 19 Sep 2022 17:01:33 GMT

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!

Re: Create a new dataset

PaigeMiller — Mon, 19 Sep 2022 17:27:10 GMT

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?

Re: Create a new dataset

ghosh — Mon, 19 Sep 2022 18:17:39 GMT

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;

Re: Create a new dataset

Tom — Mon, 19 Sep 2022 19:03:31 GMT

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

Re: Create a new dataset

walker105 — Tue, 20 Sep 2022 18:25:38 GMT

Thanks for your suggestions!