topic Re: Merging one dataset 'N' number of times in SAS Programming

Merging one dataset 'N' number of times

prasanthkambhatla — Fri, 27 Apr 2012 18:25:21 GMT

Hi:

I have a huge dataset and I wanted to merge that same dataset 1000 times. I tried do loops, i tried macros but none of them seemed to have worked.

Thanks for your help in advance.

Prasanth.

Re: Merging one dataset 'N' number of times

Linlin — Fri, 27 Apr 2012 18:32:52 GMT

are you sure you want to "merge" the same dataset? maybe you want to "set" the dataset?

no matter how many times you merge, you only get the original dataset.

example:

data have;
input id;
cards;
1
;

data want;
merge have have have;
run;
proc print;run;

obs id

1 1

Re: Merging one dataset 'N' number of times

prasanthkambhatla — Fri, 27 Apr 2012 18:35:57 GMT

I am sorry, I wanted to set the same dataset 1000 times. I am unable to do it. Could you please help?

Thanks.

Re: Merging one dataset 'N' number of times

art297 — Fri, 27 Apr 2012 18:40:10 GMT

Do you only want to expand the file 1,000 times. If so, you could use something like:

data want (drop=i);

set have;

do i=1 to 1000;

output;

end;

run;

Re: Merging one dataset 'N' number of times

Linlin — Fri, 27 Apr 2012 18:41:25 GMT

yes. try the code below:

data have;

input id $;

cards;

;

%macro test;

data want;

set %do i=1 %to 1000;

have

%end;;

run;

%mend;

%test

proc print data=want;run;

Re: Merging one dataset 'N' number of times

prasanthkambhatla — Fri, 27 Apr 2012 18:49:52 GMT

Hey Linlin:

The code worked.

Thanks.

Can you also tell me this?

I have huge dataset with 129,000 rows. However, the original dataset only contains 129 rows but with 1000 repetitions of different weights (those weights generated by random uniform distribution) assigned to each variable.

Can you tell me how to calculate the 10th percentile (p10) and 90th percentile (p90) for the each of the dataset (dataset=129 observations) within the large dataset. So that ultimately I would have 1000 p10s and 1000 p90s?

Thank you.

Re: Merging one dataset 'N' number of times

Linlin — Fri, 27 Apr 2012 18:54:03 GMT

Sorry. I don't understand the question. - Linlin

Re: Merging one dataset 'N' number of times

prasanthkambhatla — Fri, 27 Apr 2012 19:00:14 GMT

I am sorry.

I actually have a dataset that contains 129 rows and 10-15 columns.

I just "set" those datasets 1000 times using your loop and created a dataset with 129,000 rows and 10-15 columns.

Then I created a "do-loop" for generating 1000 random sets of weights and used those 1000 random sets of weights and used data transformation to create an index. So I have 1000 datasets each containing 129 observations with different weights assigned to each index variable.

Now, my questions is within each dataset which has 129 observations, I wanted to write the code to create a 10th percentile and 90th percentile for that dataset.

Are you able to understand what I am trying to say??

Thanks.

Prasanth.

Re: Merging one dataset 'N' number of times

Linlin — Fri, 27 Apr 2012 19:09:29 GMT

if you have dataset with 30 observations. which are your 10th percentile and 90th percentile?

bs i

1     1
2     2
3     3
4     4
5     5
6     6
7     7
8     8
9     9
10    10
11    11
12    12
13    13
14    14
15    15
16    16
17    17
18    18
19    19
20    20
21    21
22    22
23    23
24    24
25    25
26    26
27    27
28    28
29    29
30    30

Re: Merging one dataset 'N' number of times

prasanthkambhatla — Fri, 27 Apr 2012 19:13:23 GMT

If I have the above dataset, how to write the code for the 10th and 90th percentile for 1-10 obs, 10-20 obs, 20-30 obs, like that if I had a dataset for 129,000, i have to write a code for 1-129 obs each.

Re: Merging one dataset 'N' number of times

data_null__ — Fri, 27 Apr 2012 19:25:12 GMT

proc surveyselect data=sashelp.class out=class15 rep=15 rate=1;

run;

data class15;

set class15;

weight = rannor(124)+rannor();

run;

proc stdize

data=class15

out=_null_

outstat=pctile

pctlpts=5 10 50 90 94

;

var weight;

by replicate;

run;

Proc print;

run;

Re: Merging one dataset 'N' number of times

Linlin — Fri, 27 Apr 2012 19:27:01 GMT

Hi,

Art's code is better than the macro.

hope this helpful:

data have;
input id $;
cards;
a
;

data temp (drop=i);
set have;
do i=1 to 1000;
output;
end;
run;

data want;
set temp nobs=nobs;
if _n_/nobs <=0.1 then p=10;
else if _n_/nobs <=0.2 then p=20;
else if _n_/nobs <=0.3 then p=30;
else if _n_/nobs <=0.4 then p=40;
else if _n_/nobs <=0.5 then p=50;
else if _n_/nobs <=0.6 then p=60;
else if _n_/nobs <=0.7 then p=70;
else if _n_/nobs <=0.8 then p=80;
else if _n_/nobs <=0.9 then p=90;
else p=100;
run;
proc print;run;