Using call rantbl and call ranuni

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 131
Accepted Solution

Using call rantbl and call ranuni

Hello!

I have a data set for information on a program's participants and the participants' children, where a participant can have multiple children and the data set is not one record per child.

Instead, it is organized by participant ID and the information on the child is organized horizontally (i.e. the child variables on name, dob, age, grade, gender are:

child_firstn1, child_last1, child_dob1, child_age1, child_grade1, child_gender1

child_firstn2, child_last2, child_dob2, child_age2, child_grade2, child_gender2 etc. for 10 children)

I'm looking for a way to create two variables, s_childname and s_dob, which would be the name and DOB variables, respectively, for one chosen child of each participant.  I d like the program to select this child so that the overall sample ends up having a particular proportion of children in the three grade ranges pre-k, middle school, and high school. 

More specifically, to choose each participant's s_childname (and s_dob), the code first creates a variable called household type (hh_type), which is the concatentation of the participant's number of children in the three grade ranges prek, middle, and highschool. For example, hh_type "201"  means the participant has two children in prek, zero in middle school, and one child in high school.  There are 26 hh_types and for each hh_type, the desired sample would X% pick a kid in the prek grade range (var. p1 is x%), Y% pick a kid in the middle school grade range(var. p2 is y%), and 100-(X+Y)% pick a kid in the HS grade range (var. p3 is this %).  So, for all the participants who have 201 hh_type and p1=.5, p2=.5, p3=0,  the sample should pick a kid from the pre-k age range for 50% of the participants and a kid from the middle school grade range for the other 50% of the participants; no participants with hh_Type 201 will have their hs kid chosen.

Then, once the age range of the s_childname is chosen, the program will randomly select one of the kids in that age range to be s_child.

So far, I am using the call rantbl function to select the age range of the kid and then call ranuni to select the kid from that age range randomly.  Does this work?  What else would work?  I would truly appreciate feedback!  Code is below

data c0;

set [merging two datasets];

call rantbl(seed, p1, p2, p3, pick1);

run;

data c1;

set c0;

if pick1=1 then do;

call ranuni (seed, kidnumber);

kidnumber=int(f5*kidnumber)+1;

end;

if pick1=2 then do;

call ranuni (seed, kidnumber);

kidnumber=int(f5*kidnumber)+1;

end;

if pick1=3 then do;

call ranuni (seed, kidnumber);

kidnumber=int(f5*kidnumber)+1;

end;

run;

data drive.c2;

set c1;

%macro pick2 (numb=);

if kidnumber=&numb then do;

kid_name= ff6a_&numb||ff6b_&numb;

kid_dob= child_dob_&numb;

end;

%mend;

%pick2 (numb=1);

%pick2 (numb=2);

%pick2 (numb=3);

%pick2 (numb=4);

%pick2 (numb=5);

%pick2 (numb=6);

%pick2 (numb=7);

%pick2 (numb=8);

run;

Thank you in advance


Accepted Solutions
Solution
‎01-05-2015 02:02 PM
Super User
Posts: 10,543

Re: Using call rantbl and call ranuni

A different order to do this in one pass:

%macro child (numb);

data c (keep= child_frstnm_vld   child_lstnm_vld   child_name_vld child_dobflg   child_missng_flg  child_male_flag child_female_flag); /* and any other variables you need other than the reassigned below*/

    set drive.t10;

    %DO I= 1 %TO &numb;

        child_frstnm_vld    =child_frstnm_vld_&I;

        child_lstnm_vld    =child_lstnm_vld_&I;

        child_name_vld    =child_name_vld_&I;

        child_dobflg        =child_dobflg_&i;

        child_missng_flg=child_missng_flg_&I;

        child_male_flag=    child_male_flag_&i;

        child_female_flag=child_female_flag_&i;

         output; /* the explicit output controls when to output*/

    %end;

run;

%MEND;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,300

Re: Using call rantbl and call ranuni

Look at PROC SURVEYSELECT with the SAMPLINGUNIT and STRATA statements.

--You shouldn't post duplicates of the same question, even if it is on different communities.

Frequent Contributor
Posts: 131

Re: Using call rantbl and call ranuni

Thank you, FriedEgg.

If I do use the call rantbl function, however, do you know how I might be able to apply this for all of my 26 household types since now I don't think Im doing that?

Trusted Advisor
Posts: 1,300

Re: Using call rantbl and call ranuni

Without any code, give me an example of input and out data you want.

Frequent Contributor
Posts: 131

Re: Using call rantbl and call ranuni

Hi fried egg, I created a different thread with the question in more condensed form in case it s easier to understand.  If you can, I'd appreciate your help still.  Thanks!

I ll delete this if/once you see this.
Thanks

Trusted Advisor
Posts: 1,300

Re: Using call rantbl and call ranuni

Since there is no content in your other thread, replying here makes more sense.

I will reiterate again, that you should consider a better format for your data and use PROC SURVEYSELECT for your sampling.

%let p1=.5;

%let p2=.5;

%let p3=0;

/*Generate some fake data*/

%let today=%sysfunc(today());

proc format;

invalue dob2class

%sysfunc(intnx(year,&today.,-18,b))-%eval(%sysfunc(intnx(year,&today.,-12,m))-1)=3

%sysfunc(intnx(year,&today.,-12,m))-%eval(%sysfunc(intnx(year,&today.,-5,m))-1)=2

%sysfunc(intnx(year,&today.,-5,m))-&today.=1

;

run;

data have;

call streaminit(34635250386);

min=intnx('year',&today.,-18,'s');

diff=intnx('year',&today.,-3,'s')-min;

do hhid=1 to 1000;

do c=1 to 2+floor(ranuni(7934528)*9);

format dob1-dob10 date9.;

array dob[10];

array grade[10];

dob=min+floor(diff*rand('uniform'));

grade=input(dob, dob2class.);

end;

output;

call missing(of dob

  • , of grade
  • );
  • end;

    drop min diff c;

    stop;

    run;

    /*What I believe you are attempting?*/

    data want;

    set have;

    array dob[10] dob:;

    array a[10] grade:;

    array c[3,10] _temporary_;

    array x[3] _temporary_ (3*0);

    do _n_=1 to dim(dob) while (not missing(dob[_n_]));

    c[a[_n_],x[a[_n_]]+1]=_n_;

    x[a[_n_]]+1;

    end;

    i=rantbl(56832452,&p1.,&p2.,&p3.);

    if x ne 0 then do;

    j=1+floor((x-1)*ranuni(3232342));

    format pick_dob date9.;

    pick_dob=dob[c[i,j]];

    end;

    output;

    call missing(of c

  • );
  • call pokelong(repeat(put(0, rb8.), 2), addrlong(x[1]), %eval(3*8));

    drop i j;

    run;

    Frequent Contributor
    Posts: 131

    Re: Using call rantbl and call ranuni

    Hi Fried Egg,

    Thanks so much for your input -- I agree it would be better to make my data vertical first then use proc survey select.  Would you have input on the way I ve tried to make the data set one record per child?  I've commented above.


    Thanks so much for your feedback

    Super User
    Posts: 17,928

    Re: Using call rantbl and call ranuni

    You're better of flipping your data to be one child per record and then using proc surveyselect. 

    The wide structure is rarely beneficial to analysis.

    Frequent Contributor
    Posts: 131

    Re: Using call rantbl and call ranuni

    Hi Reeza,


    Thank you so much for your input!  It does seem it would work best for me to have my data vertical/ one child per record instead of horizontal.

    In the current horizontal data set, I have many variables which are specific to each child and I'm wondering if the following method works to make the data set one record per child:  I created 10 data sets, where the first data set (C1) renames each of the child-specific variables for child 1 and the last data set (data set c10) renames all the child-specific variables for child #10 to the generic var name. The code is below.  Then, I appended all c1-c10 data sets and had a flag for if child first name, last name, dob was missing. if all was missing, I deleted the record. 

    /*Re-arrange data to child level*/

    %macro child (numb);

    %DO I= 1 %TO &numb;

    data c_&i;

        set drive.t10;

        rename

            child_frstnm_vld_&i    =child_frstnm_vld

            child_lstnm_vld_&i    =child_lstnm_vld

            child_name_vld_&i    =child_name_vld

            child_dobflg_&i        =child_dobflg

            child_missng_flg_&i=child_missng_flg

            child_male_flag_&i=    child_male_flag;

            child_female_flag_&i=child_female_flag;

     

    run;

    %END;

    %MEND;

    %child(10);

    happy new year!  And thank you for your feedback in advance

    Solution
    ‎01-05-2015 02:02 PM
    Super User
    Posts: 10,543

    Re: Using call rantbl and call ranuni

    A different order to do this in one pass:

    %macro child (numb);

    data c (keep= child_frstnm_vld   child_lstnm_vld   child_name_vld child_dobflg   child_missng_flg  child_male_flag child_female_flag); /* and any other variables you need other than the reassigned below*/

        set drive.t10;

        %DO I= 1 %TO &numb;

            child_frstnm_vld    =child_frstnm_vld_&I;

            child_lstnm_vld    =child_lstnm_vld_&I;

            child_name_vld    =child_name_vld_&I;

            child_dobflg        =child_dobflg_&i;

            child_missng_flg=child_missng_flg_&I;

            child_male_flag=    child_male_flag_&i;

            child_female_flag=child_female_flag_&i;

             output; /* the explicit output controls when to output*/

        %end;

    run;

    %MEND;

    Frequent Contributor
    Posts: 131

    Re: Using call rantbl and call ranuni

    Hi all  - thank you so much.

    Ballardw,

    I had to make my data set vertical/one child per record to delete ineligible children and create a count variable for number of children each participant has after ineligible children are deleted.

    to create the count variable:  how would i create a variable which holds the highest number of children of the participant/adult for each child record in the data set?

    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 10 replies
    • 511 views
    • 6 likes
    • 4 in conversation