want to convert data in tabular format

Reply
Contributor
Posts: 74

want to convert data in tabular format

Dear Team

pl help

my data is in list formta .

MAPPING_RMMAPPING_RM NameCOMP_CODEcnt
ECN
13175Vikram Shakyawar11
15963Rachel Agnes Lobo21
11475Sethupathi Ramanathan31
13175Vikram Shakyawar32
14894Vidya Murudkar33
15963Rachel Agnes Lobo34
17407Avinash Kadam35
17407Avinash Kadam36
17447Nisha Netram37

And i want to convert in below attcahed format.

comp coderm_code_1Rm_Name_1Rm_Code_2Rm_name_2Rm_Code_3Rm_name_3Rm_Code_4Rm_name_4Rm_Code_5Rm_name_5Rm_Code_6Rm_name_6Rm_Code_7Rm_name_7
113175Vikram Shakyawar
215963Rachel Agnes Lobo
311475Sethupathi Ramanathan13175Vikram Shakyawar14894Vidya Murudkar15963Rachel Agnes Lobo17407Avinash Kadam17407Avinash Kadam17447Nisha Netram
Super User
Posts: 5,257

Re: want to convert data in tabular format

You can do this in a data step using explicit an OUTPUT statement.

You could also map your code/name variable to retained arrays, to avoid explicit if-then/select-when-logic for each CNT.

Data never sleeps
Regular Contributor
Posts: 151

Re: want to convert data in tabular format

There's some neat functionality in PROC SUMMARY that can do this, using IDGROUP.  Here's the code, you'd need to reorder the variables afterwards if you wanted them in exactly the same order, but the values are the same.  I've put in a step to store the maximum cnt value, which is then used to determine how many variables are created.

data have;

infile cards dsd;

input MAPPING_RM_ECN MAPPING_RM_Name $ :25. COMP_CODE cnt;

cards;

13175,Vikram Shakyawar, 1,1,

15963,Rachel Agnes Lobo, 2,1,

11475,Sethupathi Ramanathan,3,1,

13175,Vikram Shakyawar, 3,2,

14894,Vidya Murudkar, 3,3,

15963,Rachel Agnes Lobo, 3,4,

17407,Avinash Kadam, 3,5,

17407,Avinash Kadam, 3,6,

17447,Nisha Netram, 3,7

;

run;

proc sql noprint;

select max(cnt) into :maxnum

from have;

quit;

proc summary data=have nway;

class comp_code;

output out=want (drop=_Smiley Happy

  idgroup(min(cnt) out [&maxnum.] (MAPPING_RM_ECN MAPPING_RM_Name)=RM_code RM_Name);

run;

SAS Super FREQ
Posts: 683

Re: want to convert data in tabular format

Hi

this can be solved using "double transpose", see also 44637 - Double PROC TRANSPOSE method for reshaping your data set with multiple BY variables but I must say I like the version from very much too

Here is a code sample

options

  validvarname=v7

;

data have;

  infile cards dlm=",";

  input

    MAPPING_RM_ecn : 8.

    MAPPING_RM_name : $32.

    COMP_CODE : 8.

    cnt : 8.

  ;

cards;

13175,Vikram Shakyawar, 1, 1

15963,Rachel Agnes Lobo, 2, 1

11475,Sethupathi Ramanathan, 3, 1

13175,Vikram Shakyawar, 3, 2

14894,Vidya Murudkar, 3, 3

15963,Rachel Agnes Lobo, 3, 4

17407,Avinash Kadam, 3, 5

17407,Avinash Kadam, 3, 6

17447,Nisha Netram, 3, 7

;

%macro doubleTranspose(

  data=

  , by=

  , vkeys=

  , tvars=

  , out=

);

%local dsnPrefix;

%let dsnPrefix = _t_;

proc transpose data = &data out = &dsnPrefix.t1;

  by &by &vkeys;

  var &tvars;

run;

/*data &dsnPrefix.t2 ( drop = &vkeys );*/

/*  length _name_ $ 32;*/

/*  set &dsnPrefix.t1;*/

/*  array v (*) &vkeys;*/

/**/

/*  do i = 1 to dim ( v );*/

/*    _name_ = cats(_name_, vname(v), v);*/

/*  end;*/

/*run;*/

proc transpose

  data = &dsnPrefix.t1

  out = &out ( drop = _name_ )

  delimiter=_

;

  by &by;

  var col1;

  id _name_ &vkeys;

  * format _numeric_ 12.2;

run;

%mend;

options mprint;

%doubleTranspose(

  data=have

  , by=comp_code

  , vkeys=cnt

  , tvars=mapping_rm_ecn mapping_rm_name

  , out=want

)

Ask a Question
Discussion stats
  • 3 replies
  • 672 views
  • 1 like
  • 4 in conversation