topic Transpose and append data based on time interval in SAS Programming

Transpose and append data based on time interval

ari — Thu, 02 Jun 2016 15:52:48 GMT

Hi all,

I have two datsets (data1 and data2) to append based on time interval.

data1

ID	T	D	E
200	0	15	0
200	0	0	4
200	0.021	27.1	0
200	0.033	74.9	0
200	0.052	142	0
200	0.094	191	0
200	0.18	197	0
200	0.346	242	0
200	0.51	350	0
200	1.015	442	0
200	2.015	555	0
200	3.01	593	0
200	4.013	540	0
200	5.016	425	0
200	6.016	460	0
200	7.02	610	0
200	9	514	0
200	10.001	466	0
200	11.007	503	0
200	12.014	415	0
200	13.024	458	0
200	14.017	448	0
200	28.035	44.7	0
200	56.02	366	0
200	88.033	278	0
200	120.026	247	0
200	215.973	228	0
200	246.013	186	0
200	276.978	251	0
200	306.206	210	0
200	334.98	190	0
200	365.073	183	0
200	393.094	179	0
200	425.052	217	0
200	456.056	180	0
200	484.043	290	0
200	522.051	317	0
200	550.059	201	0
200	582.078	339	0
200	600.007	203	0
200	600.045	256	0
200	600.068	266	0
200	600.108	183	0
200	600.191	186	0
200	600.358	146	0
200	601.181	81.2	0
200	602.106	65	0
200	603.073	31.5	0
200	604.075	23.2	0
300	0	15.5	0
300	0	0	4
300	0.014	23.9	0
300	0.024	28	0
300	0.035	25.5	0
300	0.045	13.7	0
300	0.094	28.1	0
300	0.17	44.4	0
300	0.337	194	0
300	0.503	336	0
300	1.006	377	0
300	2.008	598	0
300	3	565	0
300	4.049	678	0
300	4.999	639	0
300	6.001	546	0
300	7.274	553	0
300	8.003	641	0
300	9.045	491	0
300	10.122	435	0
300	11.003	415	0
300	12.006	318	0
300	12.997	356	0
300	14.012	331	0
300	28.01	680	0
300	56.008	251	0
300	84.01	228	0
300	116.002	243	0
300	210.965	180	0
300	242.013	152	0
300	273.01	209	0
300	301.009	186	0
300	336.006	175	0
300	368.069	152	0
300	396.027	152	0
300	427.01	135	0
300	459.072	130	0
300	486.017	144	0
300	518.316	193	0
300	557.281	172	0
300	597.971	157	0
300	618.969	138	0
300	646.967	152	0
300	667.969	136	0
300	721.878	115	0
300	757.016	126	0
300	757.073	181	0
300	757.094	157	0
300	757.135	165	0
300	757.219	174	0
300	757.385	182	0
300	758.274	127	0
300	759.326	83.4	0
300	760.078	57.8	0
300	761.014	33	0

data2

ID	Period	T	BSE	BSS	FRI	SPE
200	1	0-90	4.5	7	27.33	4.5
200	1.5	21-110	6	8	27.33	5
200	2	91-180	4.5	9	25	7

Output dataset needed is

data3

ID	T	D	E
200	0	15	0	NA	NA	NA	NA
200	0	0	4	NA	NA	NA	NA
200	0.021	27.1	0	NA	NA	NA	NA
200	0.033	74.9	0	NA	NA	NA	NA
200	0.052	142	0	NA	NA	NA	NA
200	0.094	191	0	NA	NA	NA	NA
200	0.18	197	0	NA	NA	NA	NA
200	0.346	242	0	NA	NA	NA	NA
200	0.51	350	0	NA	NA	NA	NA
200	1.015	442	0	NA	NA	NA	NA
200	2.015	555	0	NA	NA	NA	NA
200	3.01	593	0	NA	NA	NA	NA
200	4.013	540	0	NA	NA	NA	NA
200	5.016	425	0	NA	NA	NA	NA
200	6.016	460	0	NA	NA	NA	NA
200	7.02	610	0	NA	NA	NA	NA
200	9	514	0	NA	NA	NA	NA
200	10.001	466	0	NA	NA	NA	NA
200	11.007	503	0	NA	NA	NA	NA
200	12.014	415	0	NA	NA	NA	NA
200	13.024	458	0	NA	NA	NA	NA
200	14.017	448	0	NA	NA	NA	NA
200	28.035	44.7	0	NA	NA	NA	NA
200	56.02	366	0	NA	NA	NA	NA
200	88.033	278	0	NA	NA	NA	NA
				200	BSE	4.5	5
				200	BSS	7	6
				200	FRI	27.33	7
				200	SPE	4.5	8

Basically, append needs to be done (after transposing data2) as shown above and the T variable (time interval) to be used for append. The highest value for E in data1 is 4 and E value should be incremented starting with value 5 in output dataset when its populated with a row from data2. ID is the subject ID and T is the time variable. ID and Time are the matching variables.

Any help to get this output?

Thanks in advance.

Re: Transpose and append data based on time interval

RW9 — Thu, 02 Jun 2016 11:32:03 GMT

Sorry, why? That dataset you want out at the end makes no sense whatsoever, table2 does not seem to reflect table1 in any way shape or form. For instance, why create four columns with nothing in them just to add another dataset? Is this for a report, if so just proc report the two of them with no page break in between.

Re: Transpose and append data based on time interval

ari — Thu, 02 Jun 2016 11:45:38 GMT

Hi RW9,

The output dataset is customised input to a software. Append has to happen in intervals of 90 (T variable) and each subject has several of those intervals inturn. E variable also need to be updated for each of the intervals of T variable. Is that possible with Proc Report??

Re: Transpose and append data based on time interval

RW9 — Thu, 02 Jun 2016 12:54:55 GMT

Sorry, still not making sense. What application wants SAS datase with no logical structure? All applications now should accept properly formated data, in a lot of cases XML, JSon, CSV. But it would need to be logically set, you can't do any processing on a dataset with missing ids on some of the data - if it gets sorted or modified then the whole thing becomes out of sequence and corrupted. If those values are applicable to each row, then they should be merged onto each relevant line, not left floating around at the end (or whereever they end up after processing).

Re: Transpose and append data based on time interval

ballardw — Thu, 02 Jun 2016 14:54:28 GMT

I don't see any matching going on. You dont' specify what the ID or other variables from table 2 should be named in the result set.

And what is rule (or rules) getting data2 into this order?

200	BSE	4.5	5
200	BSS	7	6
200	FRI	27.33	7
200	SPE	4.5	8

it almost appears like random mixes fo the variables as BSE does not have a value of 5 in the displayed example, BSS does not have a 6, FRI does not have a 7 and SPE does not have an 8.

Re: Transpose and append data based on time interval

ari — Thu, 02 Jun 2016 16:05:07 GMT

Hi,
for each of the row in the data2 appended to data1, a counter is initiated which starts at 5. As you can see in the output the row which contains BSE is the first row to be appended and hence is 5, BSS is 6 etc..

Re: Transpose and append data based on time interval

ballardw — Thu, 02 Jun 2016 16:25:47 GMT

I think this does what your are asking for:

data d2trans (keep=id2 code value counter);
   set data2;
   id2 = id;
   length code $3.;
   Code = 'BSE';
   Value= BSE;
   counter=5;
   output;
   Code = 'BSS';
   Value= BSS;
   counter=6;
   output;
   Code = 'FRI';
   Value= FRI;
   counter=7;
   output;
   Code = 'SPE';
   Value= SPE;
   counter=8;
   output;
run;


data want;
   set 
      data1 
      d2trans
 
   ;
run;

Re: Transpose and append data based on time interval

Ksharp — Fri, 03 Jun 2016 02:54:06 GMT

Assuming I know what you are looking for .

data data1;
infile cards truncover expandtabs;
input ID	T	D	E;
cards;
200	0	15	0
200	0	0	4
200	0.021	27.1	0
200	0.033	74.9	0
200	0.052	142	0
200	0.094	191	0
200	0.18	197	0
200	0.346	242	0
200	0.51	350	0
200	1.015	442	0
200	2.015	555	0
200	3.01	593	0
200	4.013	540	0
200	5.016	425	0
200	6.016	460	0
200	7.02	610	0
200	9	514	0
200	10.001	466	0
200	11.007	503	0
200	12.014	415	0
200	13.024	458	0
200	14.017	448	0
200	28.035	44.7	0
200	56.02	366	0
200	88.033	278	0
200	120.026	247	0
200	215.973	228	0
200	246.013	186	0
200	276.978	251	0
200	306.206	210	0
200	334.98	190	0
200	365.073	183	0
200	393.094	179	0
200	425.052	217	0
200	456.056	180	0
200	484.043	290	0
200	522.051	317	0
200	550.059	201	0
200	582.078	339	0
200	600.007	203	0
200	600.045	256	0
200	600.068	266	0
200	600.108	183	0
200	600.191	186	0
200	600.358	146	0
200	601.181	81.2	0
200	602.106	65	0
200	603.073	31.5	0
200	604.075	23.2	0
300	0	15.5	0
300	0	0	4
300	0.014	23.9	0
300	0.024	28	0
300	0.035	25.5	0
300	0.045	13.7	0
300	0.094	28.1	0
300	0.17	44.4	0
300	0.337	194	0
300	0.503	336	0
300	1.006	377	0
300	2.008	598	0
300	3	565	0
300	4.049	678	0
300	4.999	639	0
300	6.001	546	0
300	7.274	553	0
300	8.003	641	0
300	9.045	491	0
300	10.122	435	0
300	11.003	415	0
300	12.006	318	0
300	12.997	356	0
300	14.012	331	0
300	28.01	680	0
300	56.008	251	0
300	84.01	228	0
300	116.002	243	0
300	210.965	180	0
300	242.013	152	0
300	273.01	209	0
300	301.009	186	0
300	336.006	175	0
300	368.069	152	0
300	396.027	152	0
300	427.01	135	0
300	459.072	130	0
300	486.017	144	0
300	518.316	193	0
300	557.281	172	0
300	597.971	157	0
300	618.969	138	0
300	646.967	152	0
300	667.969	136	0
300	721.878	115	0
300	757.016	126	0
300	757.073	181	0
300	757.094	157	0
300	757.135	165	0
300	757.219	174	0
300	757.385	182	0
300	758.274	127	0
300	759.326	83.4	0
300	760.078	57.8	0
300	761.014	33	0
;
run;

data data2;
infile cards truncover expandtabs;
input ID	Period 	T $	BSE	BSS	FRI	SPE;
cards;
200	1	0-90	4.5	7	27.33	4.5
200	1.5	21-110	6	8	27.33	5
200	2	91-180	4.5	9	25	7
;
run;
data data2;
 set data2;
 start=input(scan(T,1,'-'),best8.);
 end=input(scan(T,2,'-'),best8.);
run;


proc sql;
 create table x as
  select a.ID as a_id,a.T as a_t,a.BSE,a.BSS,a.FRI,a.SPE,b.*
   from data2 as a,data1 as b 
    where a.id=b.id and b.T between a.start and a.end
     order by 1,2,b.t;
quit;
data want;
 do until(last.a_t);
  set x;
  by a_id a_t;
  max=max(max,E);
  output;
 end;
 
  length a_name $ 20;
  call missing(ID,T,D,E);
  a_new_id=a_id;
  a_name='BSE';a_value=BSE;a_e=max+1;output;
  a_name='BSS';a_value=BSS;a_e=max+2;output;
  a_name='FRI';a_value=FRI;a_e=max+3;output;
  a_name='SPE';a_value=SPE;a_e=max+4;output;

 drop a_id a_t max BSE BSS FRI SPE;
run;