CODEWITHSUNDEEP
sasfrequencydo-loops
Rosie Rosie
Rosie Rosie

Reputation: 21

How to change the summarized frequency back to each individual observation in SAS

I have a survey data, mothers and fathers choose the answer-age of each question, there are total 14 questions. The data 'SSR' what I got is as follows(just 2 questions for example),

Gender  Question age frequency
Father  Age8SR  7   2
Father  Age8SR  8   3
Father  Age8SR  9   2
Father  Age8SR  10  32
Father  Age8SR  10.5    1
Father  Age8SR  11  10
Father  Age8SR  12  24
Father  Age8SR  13  19
Father  Age8SR  13.5    3
Father  Age8SR  14  18
Father  Age8SR  15  11
Father  Age8SR  16  2
Father  AgeNSR  10  3
Father  AgeNSR  11  1
Father  AgeNSR  12  14
Father  AgeNSR  13  8
Father  AgeNSR  13.5    1
Father  AgeNSR  14  23
Father  AgeNSR  15  33
Father  AgeNSR  15.5    1
Father  AgeNSR  16  43

But I want to change the data like this, each observation is a replication:

Gender  Question    Rep Age Frequency
Father  Age8SR  1   7   2
Father  Age8SR  2   7   2
Father  Age8SR  3   8   3
Father  Age8SR  4   8   3
Father  Age8SR  5   8   3
Father  Age8SR  6   9   2
Father  Age8SR  7   9   2
Father  Age8SR  8   10  32
Father  Age8SR  9   10  32
Father  Age8SR  10  10  32
Father  Age8SR  11  10  32
Father  Age8SR  12  10  32
Father  Age8SR  13  10  32
Father  Age8SR  14  10  32
Father  Age8SR  15  10  32
Father  Age8SR  16  10  32
Father  Age8SR  17  10  32
Father  Age8SR  18  10  32
Father  Age8SR  19  10  32
Father  Age8SR  20  10  32
Father  Age8SR  21  10  32
Father  Age8SR  22  10  32
Father  Age8SR  23  10  32
Father  Age8SR  24  10  32
Father  Age8SR  25  10  32
Father  Age8SR  26  10  32
Father  Age8SR  27  10  32
Father  Age8SR  28  10  32
Father  Age8SR  29  10  32
Father  Age8SR  30  10  32
Father  Age8SR  31  10  32
Father  Age8SR  32  10  32
Father  Age8SR  33  10  32
Father  Age8SR  34  10  32
Father  Age8SR  35  10  32
Father  Age8SR  36  10  32
Father  Age8SR  37  10  32
Father  Age8SR  38  10  32
Father  Age8SR  39  10  32
Father  Age8SR  40  10.5    1
Father  Age8SR  41  11  10
Father  Age8SR  42  11  10
Father  Age8SR  43  11  10
Father  Age8SR  44  11  10
Father  Age8SR  45  11  10
Father  Age8SR  46  11  10
Father  Age8SR  47  11  10
Father  Age8SR  48  11  10
Father  Age8SR  49  11  10
Father  Age8SR  50  11  10
Father  Age8SR  51  12  24
Father  Age8SR  52  12  24
Father  Age8SR  53  12  24
Father  Age8SR  54  12  24
Father  Age8SR  55  12  24
Father  Age8SR  56  12  24
Father  Age8SR  57  12  24
Father  Age8SR  58  12  24
Father  Age8SR  59  12  24
Father  Age8SR  60  12  24
Father  Age8SR  61  12  24
Father  Age8SR  62  12  24
Father  Age8SR  63  12  24
Father  Age8SR  64  12  24
Father  Age8SR  65  12  24
Father  Age8SR  66  12  24
Father  Age8SR  67  12  24
Father  Age8SR  68  12  24
Father  Age8SR  69  12  24
Father  Age8SR  70  12  24
Father  Age8SR  71  12  24
Father  Age8SR  72  12  24
Father  Age8SR  73  12  24
Father  Age8SR  74  12  24
Father  Age8SR  75  13  19
Father  Age8SR  76  13  19
Father  Age8SR  77  13  19
Father  Age8SR  78  13  19
Father  Age8SR  79  13  19
Father  Age8SR  80  13  19
Father  Age8SR  81  13  19
Father  Age8SR  82  13  19
Father  Age8SR  83  13  19
Father  Age8SR  84  13  19
Father  Age8SR  85  13  19
Father  Age8SR  86  13  19
Father  Age8SR  87  13  19
Father  Age8SR  88  13  19
Father  Age8SR  89  13  19
Father  Age8SR  90  13  19
Father  Age8SR  91  13  19
Father  Age8SR  92  13  19
Father  Age8SR  93  13  19
Father  Age8SR  94  13.5    3
Father  Age8SR  95  13.5    3
Father  Age8SR  96  13.5    3
Father  Age8SR  97  14  18
Father  Age8SR  98  14  18
Father  Age8SR  99  14  18
Father  Age8SR  100 14  18
Father  Age8SR  101 14  18
Father  Age8SR  102 14  18
Father  Age8SR  103 14  18
Father  Age8SR  104 14  18
Father  Age8SR  105 14  18
Father  Age8SR  106 14  18
Father  Age8SR  107 14  18
Father  Age8SR  108 14  18
Father  Age8SR  109 14  18
Father  Age8SR  110 14  18
Father  Age8SR  111 14  18
Father  Age8SR  112 14  18
Father  Age8SR  113 14  18
Father  Age8SR  114 14  18
Father  Age8SR  115 15  11
Father  Age8SR  116 15  11
Father  Age8SR  117 15  11
Father  Age8SR  118 15  11
Father  Age8SR  119 15  11
Father  Age8SR  120 15  11
Father  Age8SR  121 15  11
Father  Age8SR  122 15  11
Father  Age8SR  123 15  11
Father  Age8SR  124 15  11
Father  Age8SR  125 15  11
Father  Age8SR  126 16  2
Father  Age8SR  127 16  2
Father  AgeNSR  1   10  3
Father  AgeNSR  2   10  3
Father  AgeNSR  3   10  3
Father  AgeNSR  4   11  1
Father  AgeNSR  5   12  14
Father  AgeNSR  6   12  14
Father  AgeNSR  7   12  14
Father  AgeNSR  8   12  14
Father  AgeNSR  9   12  14
Father  AgeNSR  10  12  14
Father  AgeNSR  11  12  14
Father  AgeNSR  12  12  14
Father  AgeNSR  13  12  14
Father  AgeNSR  14  12  14
Father  AgeNSR  15  12  14
Father  AgeNSR  16  12  14
Father  AgeNSR  17  12  14
Father  AgeNSR  18  12  14
Father  AgeNSR  19  13  8
Father  AgeNSR  20  13  8
Father  AgeNSR  21  13  8
Father  AgeNSR  22  13  8
Father  AgeNSR  23  13  8
Father  AgeNSR  24  13  8
Father  AgeNSR  25  13  8
Father  AgeNSR  26  13  8
Father  AgeNSR  27  13.5    1
Father  AgeNSR  28  14  23
Father  AgeNSR  29  14  23
Father  AgeNSR  30  14  23
Father  AgeNSR  31  14  23
Father  AgeNSR  32  14  23
Father  AgeNSR  33  14  23
Father  AgeNSR  34  14  23
Father  AgeNSR  35  14  23
Father  AgeNSR  36  14  23
Father  AgeNSR  37  14  23
Father  AgeNSR  38  14  23
Father  AgeNSR  39  14  23
Father  AgeNSR  40  14  23
Father  AgeNSR  41  14  23
Father  AgeNSR  42  14  23
Father  AgeNSR  43  14  23
Father  AgeNSR  44  14  23
Father  AgeNSR  45  14  23
Father  AgeNSR  46  14  23
Father  AgeNSR  47  14  23
Father  AgeNSR  48  14  23
Father  AgeNSR  49  14  23
Father  AgeNSR  50  14  23
Father  AgeNSR  51  15  33
Father  AgeNSR  52  15  33
Father  AgeNSR  53  15  33
Father  AgeNSR  54  15  33
Father  AgeNSR  55  15  33
Father  AgeNSR  56  15  33
Father  AgeNSR  57  15  33
Father  AgeNSR  58  15  33
Father  AgeNSR  59  15  33
Father  AgeNSR  60  15  33
Father  AgeNSR  61  15  33
Father  AgeNSR  62  15  33
Father  AgeNSR  63  15  33
Father  AgeNSR  64  15  33
Father  AgeNSR  65  15  33
Father  AgeNSR  66  15  33
Father  AgeNSR  67  15  33
Father  AgeNSR  68  15  33
Father  AgeNSR  69  15  33
Father  AgeNSR  70  15  33
Father  AgeNSR  71  15  33
Father  AgeNSR  72  15  33
Father  AgeNSR  73  15  33
Father  AgeNSR  74  15  33
Father  AgeNSR  75  15  33
Father  AgeNSR  76  15  33
Father  AgeNSR  77  15  33
Father  AgeNSR  78  15  33
Father  AgeNSR  79  15  33
Father  AgeNSR  80  15  33
Father  AgeNSR  81  15  33
Father  AgeNSR  82  15  33
Father  AgeNSR  83  15  33
Father  AgeNSR  84  15.5    1
Father  AgeNSR  85  16  43
Father  AgeNSR  86  16  43
Father  AgeNSR  87  16  43
Father  AgeNSR  88  16  43
Father  AgeNSR  89  16  43
Father  AgeNSR  90  16  43
Father  AgeNSR  91  16  43
Father  AgeNSR  92  16  43
Father  AgeNSR  93  16  43
Father  AgeNSR  94  16  43
Father  AgeNSR  95  16  43
Father  AgeNSR  96  16  43
Father  AgeNSR  97  16  43
Father  AgeNSR  98  16  43
Father  AgeNSR  99  16  43
Father  AgeNSR  100 16  43
Father  AgeNSR  101 16  43
Father  AgeNSR  102 16  43
Father  AgeNSR  103 16  43
Father  AgeNSR  104 16  43
Father  AgeNSR  105 16  43
Father  AgeNSR  106 16  43
Father  AgeNSR  107 16  43
Father  AgeNSR  108 16  43
Father  AgeNSR  109 16  43
Father  AgeNSR  110 16  43
Father  AgeNSR  111 16  43
Father  AgeNSR  112 16  43
Father  AgeNSR  113 16  43
Father  AgeNSR  114 16  43
Father  AgeNSR  115 16  43
Father  AgeNSR  116 16  43
Father  AgeNSR  117 16  43
Father  AgeNSR  118 16  43
Father  AgeNSR  119 16  43
Father  AgeNSR  120 16  43
Father  AgeNSR  121 16  43
Father  AgeNSR  122 16  43
Father  AgeNSR  123 16  43
Father  AgeNSR  124 16  43
Father  AgeNSR  125 16  43
Father  AgeNSR  126 16  43
Father  AgeNSR  127 16  43

The program I wrote:

data ssrrep;
  set ssr;  
  do question='Age8SR','AgeNSR';
     rep=0;
     do fre=1 to frequency;
        rep=rep+1;
        output;     
     end;
  end;
run; 

proc sort data=ssrrep;
   by gender question age;
run;

But it is wrong! what's the problem of my code, and how can I get the data I want?

Thanks, Rosie

Upvotes: 1

Views: 49

Answers (2)

Tom
Tom

Reputation: 51611

Your data already has the variable QUESTION. Use BY variable processing to detect when the next question starts. You need to RETAIN your REP variable. Using a sum statement (rep+1;) will retain the value across input records.

data ssrrep;
  set ssr;  
  by question ;
  if first.question then rep=0;
  do fre=1 to frequency;
    rep+1;
    output;     
  end;
run;

Upvotes: 1

Parfait
Parfait

Reputation: 107687

Consider the SQL cross join query as a solution. Essentially, the SSR dataset is cross joined with a RowNum table (one column of 1 - maximum of frequency in SSR dataset) with a row-wise conditional where clause filter.

** FIND MAX FREQUENCY (IN POSTED QUESTION = 43);
proc means data=SSR nway noprint;  
    var Frequency;
    output out=RowNum max=maxfreq;    
run;

** CREATE ROW TABLE (ONE COLUMN TABLE 1 TO MAX (I.E., 43);
data RowNum (drop=drop=_type_ _freq_ maxfreq);  
    set RowNum;
    do row = 1 to maxfreq;          
        output;
    end;    
run;

** CROSS JOIN QUERY (NO JOIN USED, ONLY LISTED TABLES);
proc sql;
    create table Final as
    select * from SSR m, RowNum r
    where r.Row <= m.Frequency;
run;

** SORT DATASET;
proc sort data=Final;
   by gender question age;
run;

** ADD REP COUNT;
data Final;
    set Final;
    by gender question age;
    if first.gender and first.question and first.age then Rep=1;
        else Rep+1;
run;

OUTPUT (N=254 RECORDS USING POSTED DATA)

Gender      Question  age  frequency   row     Rep
Father      Age8SR      7       2        1       1
Father      Age8SR      7       2        2       2
Father      Age8SR      8       3        1       3
Father      Age8SR      8       3        2       4
Father      Age8SR      8       3        3       5
Father      Age8SR      9       2        1       6
Father      Age8SR      9       2        2       7
Father      Age8SR      10      32       1       8
Father      Age8SR      10      32       2       9
Father      Age8SR      10      32       3      10
Father      Age8SR      10      32       4      11
Father      Age8SR      10      32       5      12
Father      Age8SR      10      32       6      13
Father      Age8SR      10      32       7      14
Father      Age8SR      10      32       8      15
Father      Age8SR      10      32       9      16
Father      Age8SR      10      32      10      17
Father      Age8SR      10      32      11      18
Father      Age8SR      10      32      12      19
Father      Age8SR      10      32      13      20
Father      Age8SR      10      32      14      21
Father      Age8SR      10      32      15      22
Father      Age8SR      10      32      16      23
Father      Age8SR      10      32      17      24
Father      Age8SR      10      32      18      25
Father      Age8SR      10      32      19      26
Father      Age8SR      10      32      20      27
Father      Age8SR      10      32      21      28
Father      Age8SR      10      32      22      29
Father      Age8SR      10      32      23      30
Father      Age8SR      10      32      24      31
Father      Age8SR      10      32      25      32
Father      Age8SR      10      32      26      33
Father      Age8SR      10      32      27      34
Father      Age8SR      10      32      28      35
Father      Age8SR      10      32      29      36
Father      Age8SR      10      32      30      37
Father      Age8SR      10      32      31      38
Father      Age8SR      10      32      32      39
Father      Age8SR    10.5       1       1      40
Father      Age8SR      11      10       1      41
...

Upvotes: 0

Related Questions