data normalisation

[anilgvdbm]

Dear All,

Here i have small problem in the arranging the data because my data is family and health surveys in individual level it looks like

case_ID member1 age1 sex1 dob1  member2 age2 sex2 dob2  member3 age3 sex3 dob3    member4 age4 sex4 dob4

but i need in the belowed format

case_ID    member    age  sex  dob

101                    1      54    m    4443

102                    2      32      f      5443

103                    3      43    m      3234

104                    4      21    m      2123

so please give me suggestion how to go with it.

here i attached data sample for your information.

Thanks in advance....

Anil

[LinusH]

Use the data step with arrays, do-loop and explicit output.

Search support.sas.com for "transposing data set" - there are plenty of examples and doc around this topic.

[Kurt_Bremser]

How about this:

data want (keep=case_ID member age sex dob);

set have;

array members{*} member1-member4;

array ages{*} age1-age4;

array sexs{*} sex1-sex4;

array dobs{*} dob1-dob4;

do i = 1 to 4;

  if members{i} ne .

  then do;

    member = members{i};

    age = ages{i};

    sex = sexs{i};

    dob = dobs{i};

    output;

  end;

end;

run;

[anilgvdbm]

Deal All ,

My question is raw data looks like this

Case ID

Household head

No. of memners in HH

Mem1

mem2

mem3

sex1

sex2

sex3

age1

age2

age3

but i need data arrange like in member variables i need all members names and under sex variable i need to arrange all members sex  under one by one.

like

Case ID	Household head	No. of memners in HH	Members	sex	Age
101	AB	3	mem1	sex1	age1
102	BC	2	mem2	sex2	age2
103	CD	1	mem3	sex3	age3
104	AC	5
105	AD	2
106	BD	4

so please give some idea. its huge data set please help me in this. because i am dealing with health survey data.i need to match mother and child data with household information .

Thanks and Regards,

Anil

[Kurt_Bremser]

Since the data in your .zip look different from what you describe here, I suggest you post sample "have" and "want" data here.

[anilgvdbm]

CASEID	country_code	cluster	Household_no	Individual_no	BIDX_01	BORD_01	B0_01	B1_01	B2_01	B3_01	B4_01	B5_01	B6_01	B7_01	B8_01	B9_01	B10_01	B11_01	B12_01	B13_01	BIDX_02	BORD_02	B0_02	B1_02	B2_02	B3_02	B4_02	B5_02	B6_02	B7_02	B8_02	B9_02	B10_02	B11_02	B12_02	B13_02	BIDX_03	BORD_03	B0_03	B1_03	B2_03	B3_03	B4_03	B5_03	B6_03	B7_03	B8_03	B9_03	B10_03	B11_03	B12_03	B13_03	BIDX_04	BORD_04	B0_04	B1_04	B2_04	B3_04	B4_04	B5_04	B6_04	B7_04	B8_04	B9_04	B10_04	B11_04	B12_04	B13_04
2  1 9  1	IA2	2004	9	1	1	2	0	8	88	1064	2	1			3	0	1	37			2	1	0	7	85	1027	1	0	303	36			1		37	0
2  1 17  2	IA2	2004	17	2	1	4	0	2	92	1106	1	1			0	0	1	37			2	3	0	1	89	1069	1	1			3	0	1	48	37		3	2	0	1	85	1021	2	1			7	0	1	91	48		4	1	0	6	77	930	1	1			14	0	1		91
2  1 33  1	IA2	2004	33	1	1	3	0	2	77	926	1	1			15	0	1	44			2	2	0	6	73	882	2	1			18	3	1	77	44		3	1	0	1	67	805	2	1			25	0	1		77
2  1 33  2	IA2	2004	33	2
2  1 33  3	IA2	2004	33	3	1	1	0	1	89	1069	1	1			3	0	1
2  1 41  2	IA2	2004	41	2	1	3	0	6	91	1098	1	1			0	0	1	55			2	2	0	11	86	1043	1	1			5	0	1	52	55		3	1	0	7	82	991	2	1			9	0	1		52
2  1 49  2	IA2	2004	49	2	1	3	0	8	64	776	2	1			27	3	1	31			2	2	0	1	62	745	1	1			30	0	1	37	31		3	1	0	12	58	708	2	1			33	3	1		37
2  1 57  2	IA2	2004	57	2	1	5	0	12	88	1068	2	1			3	0	1	37			2	4	0	11	85	1031	1	1			6	0	1	15	37		3	3	0	8	84	1016	2	0	107	0			1	23	15	0	4	2	0	9	82	993	2	0	304	48			1	24	23	0
2  1 65  2	IA2	2004	65	2	1	3	0	8	91	1100	1	1			0	0	1	66			2	2	0	2	86	1034	1	1			6	0	1	10	66		3	1	0	4	85	1024	1	0	105	0			5		10	0
2  1 73  2	IA2	2004	73	2	1	4	0	11	91	1103	2	0	205	5			1	24		0	2	3	0	11	89	1079	2	1			2	0	1	42	24		3	2	0	5	86	1037	2	0	302	24			1	18	42	0	4	1	0	11	84	1019	2	1			7	0	1		18
2  1 89  2	IA2	2004	89	2	1	6	0	9	71	861	1	1			20	0	1	16			2	5	0	5	70	845	1	1			22	0	1	32	16		3	4	0	9	67	813	1	1			24	3	1	25	32		4	3	0	8	65	788	1	1			26	3	1	15	25

the above data is my raw data which i have.

But i want  format like this

there are variables in the above data is BIDX_01 BIDX_02  BIDX_03 BIDX_04

and B0_01---B10_1  and B0_02---- B10_02

so i need to arrange data like

Case_ID     BIDX     B1     B2     B3     B4

101              1          1       1       1        1

102              3          2       4        2       1

i mean the variables instead of side by side i need to bring one below the another.

Thanks and Regards,

Anil

[Kurt_Bremser]

Is the target Case_ID just counting up from 101, or is there a rule for generating it?

[RW9]

Well, one way to do it would be to do the following - note that if you have lots of combinations, then pop the sql in a macro and call it each time, or generate the code using call execute.:

proc sql;

     create table WANT as

     select     CASEID,

                    BIDX_01 as BIDx,

                    B0_1 as B1,

                    B0_2 as B2,

                    ...

     from        HAVE

     union all

     select     CASEID,

                    BIDX_02 as BIDX,

                    B0_2 as B1,

                    ...

     from       HAVE

     union all

     ...

quit;

[Ksharp · Accepted solution]

Your data is a little dirty .

    data WORK.HAVE                                    ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile 'c:\temp\sample data.txt' delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat CASEID comma32. ;
       informat country_code $3. ;
       informat cluster best32. ;
       informat Household_no best32. ;
       informat Individual_no best32. ;
       informat BIDX_01 best32. ;
       informat BORD_01 best32. ;
       informat B0_01 best32. ;
       informat B1_01 best32. ;
       informat B2_01 best32. ;
       informat B3_01 best32. ;
       informat B4_01 best32. ;
       informat B5_01 best32. ;
       informat B6_01 best32. ;
       informat B7_01 best32. ;
       informat B8_01 best32. ;
       informat B9_01 best32. ;
       informat B10_01 best32. ;
       informat B11_01 best32. ;
       informat B12_01 best32. ;
       informat B13_01 best32. ;
       informat BIDX_02 best32. ;
       informat BORD_02 best32. ;
       informat B0_02 best32. ;
       informat B1_02 best32. ;
       informat B2_02 best32. ;
       informat B3_02 best32. ;
       informat B4_02 best32. ;
       informat B5_02 best32. ;
       informat B6_02 best32. ;
       informat B7_02 best32. ;
       informat B8_02 best32. ;
       informat B9_02 best32. ;
       informat B10_02 best32. ;
       informat B11_02 best32. ;
       informat B12_02 best32. ;
       informat B13_02 best32. ;
       informat BIDX_03 best32. ;
       informat BORD_03 best32. ;
       informat B0_03 best32. ;
       informat B1_03 best32. ;
       informat B2_03 best32. ;
       informat B3_03 best32. ;
       informat B4_03 best32. ;
       informat B5_03 best32. ;
       informat B6_03 best32. ;
       informat B7_03 best32. ;
       informat B8_03 best32. ;
       informat B9_03 best32. ;
       informat B10_03 best32. ;
       informat B11_03 best32. ;
       informat B12_03 best32. ;
       informat B13_03 best32. ;
       informat BIDX_04 best32. ;
       informat BORD_04 best32. ;
       informat B0_04 best32. ;
       informat B1_04 best32. ;
       informat B2_04 best32. ;
       informat B3_04 best32. ;
       informat B4_04 best32. ;
       informat B5_04 best32. ;
       informat B6_04 best32. ;
       informat B7_04 best32. ;
       informat B8_04 best32. ;
       informat B9_04 best32. ;
       informat B10_04 best32. ;
       informat B11_04 best32. ;
       informat B12_04 best32. ;
       informat B13_04 best32. ;
       format CASEID comma12. ;
       format country_code $3. ;
       format cluster best12. ;
       format Household_no best12. ;
       format Individual_no best12. ;
       format BIDX_01 best12. ;
       format BORD_01 best12. ;
       format B0_01 best12. ;
       format B1_01 best12. ;
       format B2_01 best12. ;
       format B3_01 best12. ;
       format B4_01 best12. ;
       format B5_01 best12. ;
       format B6_01 best12. ;
       format B7_01 best12. ;
       format B8_01 best12. ;
       format B9_01 best12. ;
       format B10_01 best12. ;
       format B11_01 best12. ;
       format B12_01 $1. ;
       format B13_01 best12. ;
       format BIDX_02 best12. ;
       format BORD_02 best12. ;
       format B0_02 best12. ;
       format B1_02 best12. ;
       format B2_02 best12. ;
       format B3_02 best12. ;
       format B4_02 best12. ;
       format B5_02 best12. ;
       format B6_02 best12. ;
       format B7_02 best12. ;
       format B8_02 best12. ;
       format B9_02 best12. ;
       format B10_02 best12. ;
       format B11_02 best12. ;
       format B12_02 best12. ;
       format B13_02 best12. ;
       format BIDX_03 best12. ;
       format BORD_03 best12. ;
       format B0_03 best12. ;
       format B1_03 best12. ;
       format B2_03 best12. ;
       format B3_03 best12. ;
       format B4_03 best12. ;
       format B5_03 best12. ;
       format B6_03 best12. ;
       format B7_03 best12. ;
       format B8_03 best12. ;
       format B9_03 best12. ;
       format B10_03 best12. ;
       format B11_03 best12. ;
       format B12_03 best12. ;
       format B13_03 best12. ;
       format BIDX_04 best12. ;
       format BORD_04 best12. ;
       format B0_04 best12. ;
       format B1_04 best12. ;
       format B2_04 best12. ;
       format B3_04 best12. ;
       format B4_04 best12. ;
       format B5_04 best12. ;
       format B6_04 best12. ;
       format B7_04 best12. ;
       format B8_04 best12. ;
       format B9_04 best12. ;
       format B10_04 best12. ;
       format B11_04 best12. ;
       format B12_04 best12. ;
       format B13_04 best12. ;
    input
                CASEID
                country_code $
                cluster
                Household_no
                Individual_no
                BIDX_01
                BORD_01
                B0_01
                B1_01
                B2_01
                B3_01
                B4_01
                B5_01
                B6_01
                B7_01
                B8_01
                B9_01
                B10_01
                B11_01
                B12_01 
                B13_01
                BIDX_02
                BORD_02
                B0_02
                B1_02
                B2_02
                B3_02
                B4_02
                B5_02
                B6_02
                B7_02
                B8_02
                B9_02
                B10_02
                B11_02
                B12_02
                B13_02
                BIDX_03
                BORD_03
                B0_03
                B1_03
                B2_03
                B3_03
                B4_03
                B5_03
                B6_03
                B7_03
                B8_03
                B9_03
                B10_03
                B11_03
                B12_03
                B13_03
                BIDX_04
                BORD_04
                B0_04
                B1_04
                B2_04
                B3_04
                B4_04
                B5_04
                B6_04
                B7_04
                B8_04
                B9_04
                B10_04
                B11_04
                B12_04
                B13_04
    ;
    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
    run;


data temp;
 set have;
 array _b{*} b: ;
 do i=1 to dim(_b);
  _name=vname(_b{i});
  index=scan(_name,-1,'_');
  name=scan(_name,1,'_');
  value=_b{i};
  output;
 end;
drop i b: _name ;
run;
proc transpose data=temp out=want(drop=_name_);
by CASEID  country_code  cluster Household_no Individual_no index notsorted;
var value;
id name;
run;

Xia Keshan

[anilgvdbm]

Dear Ksharp,

It's working now thanks a lot for your idea. but my raw data is in SAS 6 version files so its wont work in SAS 9.3 so i need to migrate and transpose. the datasets memory is in GB's i don't know how to process this data.

Thanks a lot once again....

Regards,

Anil

[Kurt_Bremser]

SAS can automatically deal with older dataset formats. Just read the inputs and write the new data to another library that contains 9.3 data.

Of course you WILL need enough disk space anyway.