DATA Step, Macro, Functions and more

Transpose and append data based on time interval

Reply
Frequent Contributor
Frequent Contributor
Posts: 103

Transpose and append data based on time interval

[ Edited ]

Hi all,

 

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

data1

IDTDE
2000150
200004
2000.02127.10
2000.03374.90
2000.0521420
2000.0941910
2000.181970
2000.3462420
2000.513500
2001.0154420
2002.0155550
2003.015930
2004.0135400
2005.0164250
2006.0164600
2007.026100
20095140
20010.0014660
20011.0075030
20012.0144150
20013.0244580
20014.0174480
20028.03544.70
20056.023660
20088.0332780
200120.0262470
200215.9732280
200246.0131860
200276.9782510
200306.2062100
200334.981900
200365.0731830
200393.0941790
200425.0522170
200456.0561800
200484.0432900
200522.0513170
200550.0592010
200582.0783390
200600.0072030
200600.0452560
200600.0682660
200600.1081830
200600.1911860
200600.3581460
200601.18181.20
200602.106650
200603.07331.50
200604.07523.20
300015.50
300004
3000.01423.90
3000.024280
3000.03525.50
3000.04513.70
3000.09428.10
3000.1744.40
3000.3371940
3000.5033360
3001.0063770
3002.0085980
30035650
3004.0496780
3004.9996390
3006.0015460
3007.2745530
3008.0036410
3009.0454910
30010.1224350
30011.0034150
30012.0063180
30012.9973560
30014.0123310
30028.016800
30056.0082510
30084.012280
300116.0022430
300210.9651800
300242.0131520
300273.012090
300301.0091860
300336.0061750
300368.0691520
300396.0271520
300427.011350
300459.0721300
300486.0171440
300518.3161930
300557.2811720
300597.9711570
300618.9691380
300646.9671520
300667.9691360
300721.8781150
300757.0161260
300757.0731810
300757.0941570
300757.1351650
300757.2191740
300757.3851820
300758.2741270
300759.32683.40
300760.07857.80
300761.014330

 

data2

IDPeriodTBSEBSSFRISPE
20010-904.5727.334.5
2001.521-1106827.335
200291-1804.59257

 

Output dataset needed is

data3

IDTDE    
2000150NANANANA
200004NANANANA
2000.02127.10NANANANA
2000.03374.90NANANANA
2000.0521420NANANANA
2000.0941910NANANANA
2000.181970NANANANA
2000.3462420NANANANA
2000.513500NANANANA
2001.0154420NANANANA
2002.0155550NANANANA
2003.015930NANANANA
2004.0135400NANANANA
2005.0164250NANANANA
2006.0164600NANANANA
2007.026100NANANANA
20095140NANANANA
20010.0014660NANANANA
20011.0075030NANANANA
20012.0144150NANANANA
20013.0244580NANANANA
20014.0174480NANANANA
20028.03544.70NANANANA
20056.023660NANANANA
20088.0332780NANANANA
    200BSE4.55
    200BSS76
    200FRI27.337
    200SPE4.58

 

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.

Super User
Super User
Posts: 7,942

Re: Transpose and append data based on time interval

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.

Frequent Contributor
Frequent Contributor
Posts: 103

Re: Transpose and append data based on time interval

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??
Super User
Super User
Posts: 7,942

Re: Transpose and append data based on time interval

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).

Super User
Posts: 11,343

Re: Transpose and append data based on time interval

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.

Frequent Contributor
Frequent Contributor
Posts: 103

Re: Transpose and append data based on time interval

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..
Super User
Posts: 11,343

Re: Transpose and append data based on time interval

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;
Super User
Posts: 10,020

Re: Transpose and append data based on time interval

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;
 
Ask a Question
Discussion stats
  • 7 replies
  • 335 views
  • 0 likes
  • 4 in conversation