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
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
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.
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;
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
Since the data in your .zip look different from what you describe here, I suggest you post sample "have" and "want" data here.
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
Is the target Case_ID just counting up from 101, or is there a rule for generating it?
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;
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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.