COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

Rename columns using lookup table..Plz Help

  • Follow


Hi all,
I have following 2 datasets.

1) Dataset AHU:

It has columns as:

AHUTNRX1-AHUTNRX24
ADUMNRX1-ADUMNRX24
DCUMNRX1-DCUMNRX24
U20FNRX1-U20FNRX24
ARUFNRX1-ARUFNRX24
U21DNRX1-U21DNRX24
U22DNRX1-U22DNRX24

Total number of columns = 24 * 7 = 168 columns

The first 3 letters are Product Name. Fourth letter is level code
(T=TC, M=MARKET, F=PRODUCT FAMILY, D=DOSES LEVEL).
The last 4 OR 5 letters are NRX values from 1 through 24 months.

2) Dataset Segment_Definition:
It has columns as TC, MARKET, FAMILY, DOSES, TC_CODE, MARKET_CODE,
FAMILY_CODE, DOSES_CODE.

The dataset is as follows:
TC      MARKET      FAMILY     DOSES    TC_CODE       MARKET_CODE
FAMILY_CODE      DOSES_CODE
AHY  ACD             ACU          ACU             AHU
ADU                             ADU                   ADU
AHY  DTR              B30           B30             AHU
DCU                              U41                   U41
AHY  CCA             ABZ          AB2             AHU
CAU                             U20                    U22
AHY  ACE             ACC          ACC            AHU
AEU                              ARU                   ARU
AHY  CCA             ABZ          AB1            AHU
CAU                            U20                     U22

If we look at AHU dataset then by looking at the 4th letter of every
column, we can say
AHU is TC, ADU and DCU are MARKET, U20 and ARU are PRODUCT FAMILY and
U21, U22 are PRODUCTs.

Now I have to create a new dataseet AHU_NEW.
AHU_NEW will be exact copy of AHU. Only the column names will be
renamed by using the dataset Segment_Definition.
e.g. AHUTNRX1-AHUTNRX24 will be renamed as AHYTNRX1 through AHYTNRX24.

     DCUMNRX1-DCUMNRX24 will be renamed as DTRMNRX1 through DTRMNRX24.

	If the fouth letter of a column in AHU Dataset is 'F', then consider
columns FAMILY_CODE and FAMILY.
	i.e. for U20FNRX1 column in AHU, cosider columns FAMILY_CODE and
FAMILY from Segment_Definition table.
	    Look for the value U20 in column FAMILY_CODE and the respective
value of FAMILY column is ABZ.
		Therefore in the AHU_NEW dataset  U20FNRX1-U20FNRX24 columns will be
renamed as ABZFNRX1 through ABZFNRX24.

	If the 4th letter is 'D','T' or 'M' then take DOSES_CODE and DOSES,
TC_CODE and TC, MARKET_CODE and MARKET
    columns into consideration respectively.


3) Required Dataset AHU_NEW:

It should have columns as:

AHYTNRX1-AHYTNRX24
ACDMNRX1-ACDMNRX24
DTRMNRX1-DTRMNRX24
ABZFNRX1-ABZFNRX24
ACCFNRX1-ACCFNRX24
AB1DNRX1-AB1DNRX24
AB2DNRX1-AB2DNRX24

Any help will be appreciated.
Thanks in Advance.

Regards,
Amar Mundankar.
0
Reply amarmundankar (89) 5/10/2010 11:22:13 AM 

On May 10, 6:22=A0am, Amar Mundankar <amarmundan...@gmail.com> wrote:
> Hi all,
> I have following 2 datasets.
>
> 1) Dataset AHU:
>
> It has columns as:
>
> AHUTNRX1-AHUTNRX24
> ADUMNRX1-ADUMNRX24
> DCUMNRX1-DCUMNRX24
> U20FNRX1-U20FNRX24
> ARUFNRX1-ARUFNRX24
> U21DNRX1-U21DNRX24
> U22DNRX1-U22DNRX24
>
> Total number of columns =3D 24 * 7 =3D 168 columns
>
> The first 3 letters are Product Name. Fourth letter is level code
> (T=3DTC, M=3DMARKET, F=3DPRODUCT FAMILY, D=3DDOSES LEVEL).
> The last 4 OR 5 letters are NRX values from 1 through 24 months.
>
> 2) Dataset Segment_Definition:
> It has columns as TC, MARKET, FAMILY, DOSES, TC_CODE, MARKET_CODE,
> FAMILY_CODE, DOSES_CODE.
>
> The dataset is as follows:
> TC =A0 =A0 =A0MARKET =A0 =A0 =A0FAMILY =A0 =A0 DOSES =A0 =A0TC_CODE =A0 =
=A0 =A0 MARKET_CODE
> FAMILY_CODE =A0 =A0 =A0DOSES_CODE
> AHY =A0ACD =A0 =A0 =A0 =A0 =A0 =A0 ACU =A0 =A0 =A0 =A0 =A0ACU =A0 =A0 =A0=
 =A0 =A0 =A0 AHU
> ADU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU
> AHY =A0DTR =A0 =A0 =A0 =A0 =A0 =A0 =A0B30 =A0 =A0 =A0 =A0 =A0 B30 =A0 =A0=
 =A0 =A0 =A0 =A0 AHU
> DCU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U41 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U41
> AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB2 =A0 =A0 =A0=
 =A0 =A0 =A0 AHU
> CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U20 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U22
> AHY =A0ACE =A0 =A0 =A0 =A0 =A0 =A0 ACC =A0 =A0 =A0 =A0 =A0ACC =A0 =A0 =A0=
 =A0 =A0 =A0AHU
> AEU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ARU =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ARU
> AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB1 =A0 =A0 =A0=
 =A0 =A0 =A0AHU
> CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U20 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U22
>
> If we look at AHU dataset then by looking at the 4th letter of every
> column, we can say
> AHU is TC, ADU and DCU are MARKET, U20 and ARU are PRODUCT FAMILY and
> U21, U22 are PRODUCTs.
>
> Now I have to create a new dataseet AHU_NEW.
> AHU_NEW will be exact copy of AHU. Only the column names will be
> renamed by using the dataset Segment_Definition.
> e.g. AHUTNRX1-AHUTNRX24 will be renamed as AHYTNRX1 through AHYTNRX24.
>
> =A0 =A0 =A0DCUMNRX1-DCUMNRX24 will be renamed as DTRMNRX1 through DTRMNRX=
24.
>
> =A0 =A0 =A0 =A0 If the fouth letter of a column in AHU Dataset is 'F', th=
en consider
> columns FAMILY_CODE and FAMILY.
> =A0 =A0 =A0 =A0 i.e. for U20FNRX1 column in AHU, cosider columns FAMILY_C=
ODE and
> FAMILY from Segment_Definition table.
> =A0 =A0 =A0 =A0 =A0 =A0 Look for the value U20 in column FAMILY_CODE and =
the respective
> value of FAMILY column is ABZ.
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Therefore in the AHU_NEW dataset =A0U20FN=
RX1-U20FNRX24 columns will be
> renamed as ABZFNRX1 through ABZFNRX24.
>
> =A0 =A0 =A0 =A0 If the 4th letter is 'D','T' or 'M' then take DOSES_CODE =
and DOSES,
> TC_CODE and TC, MARKET_CODE and MARKET
> =A0 =A0 columns into consideration respectively.
>
> 3) Required Dataset AHU_NEW:
>
> It should have columns as:
>
> AHYTNRX1-AHYTNRX24
> ACDMNRX1-ACDMNRX24
> DTRMNRX1-DTRMNRX24
> ABZFNRX1-ABZFNRX24
> ACCFNRX1-ACCFNRX24
> AB1DNRX1-AB1DNRX24
> AB2DNRX1-AB2DNRX24
>
> Any help will be appreciated.
> Thanks in Advance.
>
> Regards,
> Amar Mundankar.

"storing" so much data in variable names is not a good idea.

To do the rename you need
    1) list of names.
    2) a lookup table

I used INFORMATS for the lookup table.  You could just as well used a
data set.  I just sorta got it in my head the formats would be good.

The lookup table you provided was not unique.  I changed it, don't
know if I fixed it.

data test;
   length
      AHUTNRX1-AHUTNRX24
      ADUMNRX1-ADUMNRX24
      DCUMNRX1-DCUMNRX24
      U20FNRX1-U20FNRX24
      ARUFNRX1-ARUFNRX24
      U21DNRX1-U21DNRX24
      U22DNRX1-U22DNRX24 $1;
   stop;
   call missing(of _all_);
   run;
proc transpose data=3Dtest(obs=3D0) out=3Dvars;
   var _all_;
   run;
proc datasets nowarn;
   delete formats / memtype=3Dcatalog;
   run;
   quit;
data control;
   retain fmtname 'switch' type 'J' hlo 'UJ ';
   infile cards firstobs=3D2;
   array to[4] $3;
   array fr[4] $3;
   drop to: fr:;
   input to[*] fr[*];

   do _n_ =3D 1 to dim(to);
      fmtname =3D cats('SWT_',substr('TMFD',_n_,1));
      start =3D fr[_n_];
      label =3D to[_n_];
      output;
      end;
   output;
   cards;
TC  MARKET FAMILY DOSES TC_CODE  MARKET_CODE FAMILY_CODE DOSES_CODE
AHY ACD    ACU    ACU   AHU      ADU         ADU         ADU
AHY DTR    B30    B30   AHU      DCU         U41         U41
AHY CCA    ABZ    AB2   AHU      CAU         U20         U22
AHY ACE    ACC    ACC   AHU      AEU         ARU         ARU
AHY CCA    ABZ    AB1   AHU      CAU         U22         U21
;;;;
   run;
proc sort data=3Dcontrol nodupkey;
   by fmtname start label;
   run;
proc format cntlin=3Dcontrol cntlout=3Dcntlout;
   run;
proc print;
   run;
filename FT44F001 temp;
data _null_;
   file FT44F001;
   set vars end=3Deof;
   if _n_ eq 1 then put +3 'rename';
   newname =3D _name_;
   substr(newname,1,3) =3D inputC(substr(_name_,
1,3),cats('SWT_',substr(_name_,4,1)));
   put +9 _name_ '=3D' newname;
   if eof then put +9 ';';
   run;

proc datasets;
   modify test;
   %inc FT44F001 / source2;
   run;
   quit;
0
Reply data 5/10/2010 12:59:55 PM 


On May 10, 1:22=A0pm, Amar Mundankar <amarmundan...@gmail.com> wrote:
> Hi all,
> I have following 2 datasets.
>
> 1) Dataset AHU:
>
> It has columns as:
>
> AHUTNRX1-AHUTNRX24
> ADUMNRX1-ADUMNRX24
> DCUMNRX1-DCUMNRX24
> U20FNRX1-U20FNRX24
> ARUFNRX1-ARUFNRX24
> U21DNRX1-U21DNRX24
> U22DNRX1-U22DNRX24
>
> Total number of columns =3D 24 * 7 =3D 168 columns
>
> The first 3 letters are Product Name. Fourth letter is level code
> (T=3DTC, M=3DMARKET, F=3DPRODUCT FAMILY, D=3DDOSES LEVEL).
> The last 4 OR 5 letters are NRX values from 1 through 24 months.
>
> 2) Dataset Segment_Definition:
> It has columns as TC, MARKET, FAMILY, DOSES, TC_CODE, MARKET_CODE,
> FAMILY_CODE, DOSES_CODE.
>
> The dataset is as follows:
> TC =A0 =A0 =A0MARKET =A0 =A0 =A0FAMILY =A0 =A0 DOSES =A0 =A0TC_CODE =A0 =
=A0 =A0 MARKET_CODE
> FAMILY_CODE =A0 =A0 =A0DOSES_CODE
> AHY =A0ACD =A0 =A0 =A0 =A0 =A0 =A0 ACU =A0 =A0 =A0 =A0 =A0ACU =A0 =A0 =A0=
 =A0 =A0 =A0 AHU
> ADU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU
> AHY =A0DTR =A0 =A0 =A0 =A0 =A0 =A0 =A0B30 =A0 =A0 =A0 =A0 =A0 B30 =A0 =A0=
 =A0 =A0 =A0 =A0 AHU
> DCU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U41 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U41
> AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB2 =A0 =A0 =A0=
 =A0 =A0 =A0 AHU
> CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U20 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U22
> AHY =A0ACE =A0 =A0 =A0 =A0 =A0 =A0 ACC =A0 =A0 =A0 =A0 =A0ACC =A0 =A0 =A0=
 =A0 =A0 =A0AHU
> AEU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ARU =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ARU
> AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB1 =A0 =A0 =A0=
 =A0 =A0 =A0AHU
> CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U20 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U22
>
> If we look at AHU dataset then by looking at the 4th letter of every
> column, we can say
> AHU is TC, ADU and DCU are MARKET, U20 and ARU are PRODUCT FAMILY and
> U21, U22 are PRODUCTs.
>
> Now I have to create a new dataseet AHU_NEW.
> AHU_NEW will be exact copy of AHU. Only the column names will be
> renamed by using the dataset Segment_Definition.
> e.g. AHUTNRX1-AHUTNRX24 will be renamed as AHYTNRX1 through AHYTNRX24.
>
> =A0 =A0 =A0DCUMNRX1-DCUMNRX24 will be renamed as DTRMNRX1 through DTRMNRX=
24.
>
> =A0 =A0 =A0 =A0 If the fouth letter of a column in AHU Dataset is 'F', th=
en consider
> columns FAMILY_CODE and FAMILY.
> =A0 =A0 =A0 =A0 i.e. for U20FNRX1 column in AHU, cosider columns FAMILY_C=
ODE and
> FAMILY from Segment_Definition table.
> =A0 =A0 =A0 =A0 =A0 =A0 Look for the value U20 in column FAMILY_CODE and =
the respective
> value of FAMILY column is ABZ.
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Therefore in the AHU_NEW dataset =A0U20FN=
RX1-U20FNRX24 columns will be
> renamed as ABZFNRX1 through ABZFNRX24.
>
> =A0 =A0 =A0 =A0 If the 4th letter is 'D','T' or 'M' then take DOSES_CODE =
and DOSES,
> TC_CODE and TC, MARKET_CODE and MARKET
> =A0 =A0 columns into consideration respectively.
>
> 3) Required Dataset AHU_NEW:
>
> It should have columns as:
>
> AHYTNRX1-AHYTNRX24
> ACDMNRX1-ACDMNRX24
> DTRMNRX1-DTRMNRX24
> ABZFNRX1-ABZFNRX24
> ACCFNRX1-ACCFNRX24
> AB1DNRX1-AB1DNRX24
> AB2DNRX1-AB2DNRX24
>
> Any help will be appreciated.
> Thanks in Advance.
>
> Regards,
> Amar Mundankar.

Can you do without the lookup table and just use a rename staement
such as the following?

RENAME
AHUTNRX1-AHUTNRX24=3DAHYTNRX1-AHYTNRX24
ADUMNRX1-ADUMNRX24=3DACDMNRX1-ACDMNRX24
DCUMNRX1-DCUMNRX24=3DDTRMNRX1-DTRMNRX24
U20FNRX1-U20FNRX24=3DABZFNRX1-ABZFNRX24
ARUFNRX1-ARUFNRX24=3DACCFNRX1-ACCFNRX24
U21DNRX1-U21DNRX24=3DAB1DNRX1-AB1DNRX24
U22DNRX1-U22DNRX24=3DAB2DNRX1-AB2DNRX24
;

If you need it to be more complicated and to use the lookup dataset
then you could always create formats from it and use the formats to
generate the rename statement in a macro.
0
Reply RolandRB 5/10/2010 2:05:09 PM 

On May 10, 1:22=A0pm, Amar Mundankar <amarmundan...@gmail.com> wrote:
> Hi all,
> I have following 2 datasets.
>
> 1) Dataset AHU:
>
> It has columns as:
>
> AHUTNRX1-AHUTNRX24
> ADUMNRX1-ADUMNRX24
> DCUMNRX1-DCUMNRX24
> U20FNRX1-U20FNRX24
> ARUFNRX1-ARUFNRX24
> U21DNRX1-U21DNRX24
> U22DNRX1-U22DNRX24
>
> Total number of columns =3D 24 * 7 =3D 168 columns
>
> The first 3 letters are Product Name. Fourth letter is level code
> (T=3DTC, M=3DMARKET, F=3DPRODUCT FAMILY, D=3DDOSES LEVEL).
> The last 4 OR 5 letters are NRX values from 1 through 24 months.
>
> 2) Dataset Segment_Definition:
> It has columns as TC, MARKET, FAMILY, DOSES, TC_CODE, MARKET_CODE,
> FAMILY_CODE, DOSES_CODE.
>
> The dataset is as follows:
> TC =A0 =A0 =A0MARKET =A0 =A0 =A0FAMILY =A0 =A0 DOSES =A0 =A0TC_CODE =A0 =
=A0 =A0 MARKET_CODE
> FAMILY_CODE =A0 =A0 =A0DOSES_CODE
> AHY =A0ACD =A0 =A0 =A0 =A0 =A0 =A0 ACU =A0 =A0 =A0 =A0 =A0ACU =A0 =A0 =A0=
 =A0 =A0 =A0 AHU
> ADU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU
> AHY =A0DTR =A0 =A0 =A0 =A0 =A0 =A0 =A0B30 =A0 =A0 =A0 =A0 =A0 B30 =A0 =A0=
 =A0 =A0 =A0 =A0 AHU
> DCU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U41 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U41
> AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB2 =A0 =A0 =A0=
 =A0 =A0 =A0 AHU
> CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U20 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U22
> AHY =A0ACE =A0 =A0 =A0 =A0 =A0 =A0 ACC =A0 =A0 =A0 =A0 =A0ACC =A0 =A0 =A0=
 =A0 =A0 =A0AHU
> AEU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ARU =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ARU
> AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB1 =A0 =A0 =A0=
 =A0 =A0 =A0AHU
> CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U20 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U22
>
> If we look at AHU dataset then by looking at the 4th letter of every
> column, we can say
> AHU is TC, ADU and DCU are MARKET, U20 and ARU are PRODUCT FAMILY and
> U21, U22 are PRODUCTs.
>
> Now I have to create a new dataseet AHU_NEW.
> AHU_NEW will be exact copy of AHU. Only the column names will be
> renamed by using the dataset Segment_Definition.
> e.g. AHUTNRX1-AHUTNRX24 will be renamed as AHYTNRX1 through AHYTNRX24.
>
> =A0 =A0 =A0DCUMNRX1-DCUMNRX24 will be renamed as DTRMNRX1 through DTRMNRX=
24.
>
> =A0 =A0 =A0 =A0 If the fouth letter of a column in AHU Dataset is 'F', th=
en consider
> columns FAMILY_CODE and FAMILY.
> =A0 =A0 =A0 =A0 i.e. for U20FNRX1 column in AHU, cosider columns FAMILY_C=
ODE and
> FAMILY from Segment_Definition table.
> =A0 =A0 =A0 =A0 =A0 =A0 Look for the value U20 in column FAMILY_CODE and =
the respective
> value of FAMILY column is ABZ.
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Therefore in the AHU_NEW dataset =A0U20FN=
RX1-U20FNRX24 columns will be
> renamed as ABZFNRX1 through ABZFNRX24.
>
> =A0 =A0 =A0 =A0 If the 4th letter is 'D','T' or 'M' then take DOSES_CODE =
and DOSES,
> TC_CODE and TC, MARKET_CODE and MARKET
> =A0 =A0 columns into consideration respectively.
>
> 3) Required Dataset AHU_NEW:
>
> It should have columns as:
>
> AHYTNRX1-AHYTNRX24
> ACDMNRX1-ACDMNRX24
> DTRMNRX1-DTRMNRX24
> ABZFNRX1-ABZFNRX24
> ACCFNRX1-ACCFNRX24
> AB1DNRX1-AB1DNRX24
> AB2DNRX1-AB2DNRX24
>
> Any help will be appreciated.
> Thanks in Advance.
>
> Regards,
> Amar Mundankar.

I think this works. Note that you mapped U21 wrongly in your lookup
dataset so I fixed it for you. Now for a beer.

data ahu;
retain
AHUTNRX1-AHUTNRX24
ADUMNRX1-ADUMNRX24
DCUMNRX1-DCUMNRX24
U20FNRX1-U20FNRX24
ARUFNRX1-ARUFNRX24
U21DNRX1-U21DNRX24
U22DNRX1-U22DNRX24
99
;
run;
proc contents data=3Dahu out=3Dahucont(keep=3Dname) noprint;
run;
data ahucont2;
  length code $ 4;
  set ahucont;
  code=3Dname;
run;
proc print data=3Dahucont2;
run;

data dseg;
informat
TC      MARKET      FAMILY     DOSES    TC_CODE       MARKET_CODE
FAMILY_CODE      DOSES_CODE
$3.
;
input
TC      MARKET      FAMILY     DOSES    TC_CODE       MARKET_CODE
FAMILY_CODE      DOSES_CODE
;
cards;
AHY  ACD             ACU          ACU             AHU
ADU                             ADU                   ADU
AHY  DTR              B30           B30             AHU
DCU                              U41                   U41
AHY  CCA             ABZ          AB2             AHU
CAU                             U20                    U22
AHY  ACE             ACC          ACC            AHU
AEU                              ARU                   ARU
AHY  CCA             ABZ          AB1            AHU
CAU                            U20                     U21
;
run;

proc print data=3Ddseg;
run;

data dseg2;
  length code $ 4 fmt $ 4;
  set dseg;
  code=3Dtc_code||"T";fmt=3Dtc||"T";output;
  code=3Dmarket_code||"M";fmt=3Dmarket||"M";output;
  code=3Dfamily_code||"F";fmt=3Dfamily||"F";output;
  code=3Ddoses_code||"D";fmt=3Ddoses||"D";output;
  keep code fmt;
run;
proc sort data=3Ddseg2;
by code;
run;
proc print data=3Ddseg2;
run;
data dseg3;
  length newname $ 9;
  merge dseg2 ahucont2(in=3D_ahu);
  by code;
  if _ahu;
  newname=3Dfmt||substr(name,5);
  keep name newname;
run;
proc print data=3Ddseg3;
run;
filename renlist temp;
data _null_;
  set dseg3;
  file renlist;
  put name "=3D" newname;
run;
data ahu_new;
set ahu;
run;
proc datasets;
modify ahu_new;
rename
%inc renlist / source2;
;
run;
quit;
proc print data=3Dahu_new;
run;
0
Reply RolandRB 5/10/2010 3:14:59 PM 

On May 10, 8:14=A0pm, RolandRB <rolandbe...@hotmail.com> wrote:
> On May 10, 1:22=A0pm, Amar Mundankar <amarmundan...@gmail.com> wrote:
>
>
>
>
>
> > Hi all,
> > I have following 2 datasets.
>
> > 1) Dataset AHU:
>
> > It has columns as:
>
> > AHUTNRX1-AHUTNRX24
> > ADUMNRX1-ADUMNRX24
> > DCUMNRX1-DCUMNRX24
> > U20FNRX1-U20FNRX24
> > ARUFNRX1-ARUFNRX24
> > U21DNRX1-U21DNRX24
> > U22DNRX1-U22DNRX24
>
> > Total number of columns =3D 24 * 7 =3D 168 columns
>
> > The first 3 letters are Product Name. Fourth letter is level code
> > (T=3DTC, M=3DMARKET, F=3DPRODUCT FAMILY, D=3DDOSES LEVEL).
> > The last 4 OR 5 letters are NRX values from 1 through 24 months.
>
> > 2) Dataset Segment_Definition:
> > It has columns as TC, MARKET, FAMILY, DOSES, TC_CODE, MARKET_CODE,
> > FAMILY_CODE, DOSES_CODE.
>
> > The dataset is as follows:
> > TC =A0 =A0 =A0MARKET =A0 =A0 =A0FAMILY =A0 =A0 DOSES =A0 =A0TC_CODE =A0=
 =A0 =A0 MARKET_CODE
> > FAMILY_CODE =A0 =A0 =A0DOSES_CODE
> > AHY =A0ACD =A0 =A0 =A0 =A0 =A0 =A0 ACU =A0 =A0 =A0 =A0 =A0ACU =A0 =A0 =
=A0 =A0 =A0 =A0 AHU
> > ADU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU
> > AHY =A0DTR =A0 =A0 =A0 =A0 =A0 =A0 =A0B30 =A0 =A0 =A0 =A0 =A0 B30 =A0 =
=A0 =A0 =A0 =A0 =A0 AHU
> > DCU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U41 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U41
> > AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB2 =A0 =A0 =
=A0 =A0 =A0 =A0 AHU
> > CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U20 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U22
> > AHY =A0ACE =A0 =A0 =A0 =A0 =A0 =A0 ACC =A0 =A0 =A0 =A0 =A0ACC =A0 =A0 =
=A0 =A0 =A0 =A0AHU
> > AEU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ARU =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ARU
> > AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB1 =A0 =A0 =
=A0 =A0 =A0 =A0AHU
> > CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U20 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U22
>
> > If we look at AHU dataset then by looking at the 4th letter of every
> > column, we can say
> > AHU is TC, ADU and DCU are MARKET, U20 and ARU are PRODUCT FAMILY and
> > U21, U22 are PRODUCTs.
>
> > Now I have to create a new dataseet AHU_NEW.
> > AHU_NEW will be exact copy of AHU. Only the column names will be
> > renamed by using the dataset Segment_Definition.
> > e.g. AHUTNRX1-AHUTNRX24 will be renamed as AHYTNRX1 through AHYTNRX24.
>
> > =A0 =A0 =A0DCUMNRX1-DCUMNRX24 will be renamed as DTRMNRX1 through DTRMN=
RX24.
>
> > =A0 =A0 =A0 =A0 If the fouth letter of a column in AHU Dataset is 'F', =
then consider
> > columns FAMILY_CODE and FAMILY.
> > =A0 =A0 =A0 =A0 i.e. for U20FNRX1 column in AHU, cosider columns FAMILY=
_CODE and
> > FAMILY from Segment_Definition table.
> > =A0 =A0 =A0 =A0 =A0 =A0 Look for the value U20 in column FAMILY_CODE an=
d the respective
> > value of FAMILY column is ABZ.
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Therefore in the AHU_NEW dataset =A0U20=
FNRX1-U20FNRX24 columns will be
> > renamed as ABZFNRX1 through ABZFNRX24.
>
> > =A0 =A0 =A0 =A0 If the 4th letter is 'D','T' or 'M' then take DOSES_COD=
E and DOSES,
> > TC_CODE and TC, MARKET_CODE and MARKET
> > =A0 =A0 columns into consideration respectively.
>
> > 3) Required Dataset AHU_NEW:
>
> > It should have columns as:
>
> > AHYTNRX1-AHYTNRX24
> > ACDMNRX1-ACDMNRX24
> > DTRMNRX1-DTRMNRX24
> > ABZFNRX1-ABZFNRX24
> > ACCFNRX1-ACCFNRX24
> > AB1DNRX1-AB1DNRX24
> > AB2DNRX1-AB2DNRX24
>
> > Any help will be appreciated.
> > Thanks in Advance.
>
> > Regards,
> > Amar Mundankar.
>
> I think this works. Note that you mapped U21 wrongly in your lookup
> dataset so I fixed it for you. Now for a beer.
>
> data ahu;
> retain
> AHUTNRX1-AHUTNRX24
> ADUMNRX1-ADUMNRX24
> DCUMNRX1-DCUMNRX24
> U20FNRX1-U20FNRX24
> ARUFNRX1-ARUFNRX24
> U21DNRX1-U21DNRX24
> U22DNRX1-U22DNRX24
> 99
> ;
> run;
> proc contents data=3Dahu out=3Dahucont(keep=3Dname) noprint;
> run;
> data ahucont2;
> =A0 length code $ 4;
> =A0 set ahucont;
> =A0 code=3Dname;
> run;
> proc print data=3Dahucont2;
> run;
>
> data dseg;
> informat
> TC =A0 =A0 =A0MARKET =A0 =A0 =A0FAMILY =A0 =A0 DOSES =A0 =A0TC_CODE =A0 =
=A0 =A0 MARKET_CODE
> FAMILY_CODE =A0 =A0 =A0DOSES_CODE
> $3.
> ;
> input
> TC =A0 =A0 =A0MARKET =A0 =A0 =A0FAMILY =A0 =A0 DOSES =A0 =A0TC_CODE =A0 =
=A0 =A0 MARKET_CODE
> FAMILY_CODE =A0 =A0 =A0DOSES_CODE
> ;
> cards;
> AHY =A0ACD =A0 =A0 =A0 =A0 =A0 =A0 ACU =A0 =A0 =A0 =A0 =A0ACU =A0 =A0 =A0=
 =A0 =A0 =A0 AHU
> ADU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 ADU
> AHY =A0DTR =A0 =A0 =A0 =A0 =A0 =A0 =A0B30 =A0 =A0 =A0 =A0 =A0 B30 =A0 =A0=
 =A0 =A0 =A0 =A0 AHU
> DCU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U41 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U41
> AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB2 =A0 =A0 =A0=
 =A0 =A0 =A0 AHU
> CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U20 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U22
> AHY =A0ACE =A0 =A0 =A0 =A0 =A0 =A0 ACC =A0 =A0 =A0 =A0 =A0ACC =A0 =A0 =A0=
 =A0 =A0 =A0AHU
> AEU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ARU =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ARU
> AHY =A0CCA =A0 =A0 =A0 =A0 =A0 =A0 ABZ =A0 =A0 =A0 =A0 =A0AB1 =A0 =A0 =A0=
 =A0 =A0 =A0AHU
> CAU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0U20 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 U21
> ;
> run;
>
> proc print data=3Ddseg;
> run;
>
> data dseg2;
> =A0 length code $ 4 fmt $ 4;
> =A0 set dseg;
> =A0 code=3Dtc_code||"T";fmt=3Dtc||"T";output;
> =A0 code=3Dmarket_code||"M";fmt=3Dmarket||"M";output;
> =A0 code=3Dfamily_code||"F";fmt=3Dfamily||"F";output;
> =A0 code=3Ddoses_code||"D";fmt=3Ddoses||"D";output;
> =A0 keep code fmt;
> run;
> proc sort data=3Ddseg2;
> by code;
> run;
> proc print data=3Ddseg2;
> run;
> data dseg3;
> =A0 length newname $ 9;
> =A0 merge dseg2 ahucont2(in=3D_ahu);
> =A0 by code;
> =A0 if _ahu;
> =A0 newname=3Dfmt||substr(name,5);
> =A0 keep name newname;
> run;
> proc print data=3Ddseg3;
> run;
> filename renlist temp;
> data _null_;
> =A0 set dseg3;
> =A0 file renlist;
> =A0 put name "=3D" newname;
> run;
> data ahu_new;
> set ahu;
> run;
> proc datasets;
> modify ahu_new;
> rename
> %inc renlist / source2;
> ;
> run;
> quit;
> proc print data=3Dahu_new;
> run;- Hide quoted text -
>
> - Show quoted text -

Hi RolandDB, data_null,
thanks for your help. It is working for me.
You both are correct. In the last record of Segment_Definition dataset
the value should be U21.
Thanks for correcting.
Cheers.....


Amar Mundankar.
0
Reply Amar 5/11/2010 6:33:31 AM
comp.soft-sys.sas 131511 articles. 17 followers.

4 Replies
175 Views

(page loaded in 0.153 seconds)

Similiar Articles:

RENAME Column - comp.databases.ms-access
rename worksheets - comp.soft-sys.sas Rename columns using lookup table..Plz Help - comp.soft-sys.sas ... output; end ... Microsoft Excel spreadsheet file... the first ...

rename worksheets - comp.soft-sys.sas
... comp.soft-sys.matlab ... rename worksheets - comp.soft-sys.sas Naming columns in output excel files - comp.soft-sys.matlab ... Rename columns using lookup table..Plz Help ...

Rename File with SAS - comp.soft-sys.sas
Rename columns using lookup table..Plz Help - comp.soft-sys.sas ... Using and external file as a lookup table to update values in a SAS dataset.

How to Freeze Columns in a html table Using Javascript and HTML ...
Rename columns using lookup table..Plz Help - comp.soft-sys.sas ... How to Freeze Columns in a html table Using Javascript and HTML ... RENAME Column - comp.databases.ms ...

MS Access - Update Fails from SAS - comp.soft-sys.sas
Rename columns using lookup table..Plz Help - comp.soft-sys.sas ... MS Access - Update Fails from SAS - comp.soft-sys.sas RENAME Column - comp.databases.ms-access ...

Naming columns in output excel files - comp.soft-sys.matlab ...
Rename columns using lookup table..Plz Help - comp.soft-sys.sas ... rename worksheets - comp.soft-sys.sas... comp.soft-sys.matlab ... rename worksheets - comp.soft-sys.sas ...

Drag and drop table columns - comp.lang.javascript
... drop stuff, can you get me started please ... RENAME Column - comp.databases.ms-access Is there an ... ms-access Will be grateful for any help I want to show a 3 column table of ...

how to put JTextfield and JButton in a cell of JTable - comp.lang ...
... JTable table, Object value, boolean isSelected, int row, int column) It passes ... action on lookup button ... please help me in creating a form using Swing. - comp.lang ...

regarding summary table on sashelp.class dataset???? - comp.soft ...
... dataset, but i am struck in the last step. Please help ... data=final nowindows headline spacing=1; column ... code is one example of outputting a summary table of ... Renaming ...

Combine Values Vertically - Please Help - comp.soft-sys.sas ...
... Excel table ... values using vlookup in excel | Get Digital ... Merge two columns into one list Lookup with ... reach $B$1300 or more the result will be #VALUE! Please help ...

RENAME Column - comp.databases.ms-access | Computer Group
rename worksheets - comp.soft-sys.sas Rename columns using lookup table..Plz Help - comp.soft-sys.sas ... output; end ... Microsoft Excel spreadsheet file... the first ...

Visual Basic :: Rename Column In A Table?
Is it possible to rename a column name in a SQL Server 2000 table using ... (Note: I'm using SQL Server 2000) Please help me out. ... How can i lookup a column in a table or ...

7/10/2012 2:06:03 AM


Reply:



Privacy Policy | All Times Are GMT(UTC) | powered by