Repeat datalines when creating a new variable in dataset

KALLEN — Wed, 01 May 2019 22:28:36 GMT

I have a dataset A that has 96 hourly observations (var_A). I also have a dataset B that has 24 average hourly observations (var_B).

I want to combine the datasets A & B to make dataset C (attached).

How do I repeat the datalines of var_B from dataset B so that dataset C shows the same 24 average hourly values over and over until the end of the dataset A?

data c;
	set c;
	input avg_delivered_kw @@;
	datalines;
1.2075434357
1.1069760113
1.0151325226
0.9682368384
1.0312725859
1.0515738245
0.8532573946
0.6135882055
0.4793938309
0.4483874046
0.5043064344
0.5824541904
0.7168952859
0.9070687706
1.1672568734
1.6091569573
2.1673370093
2.5960530086
2.5846035421
2.4253723858
2.2624625265
1.9173588172
1.5982197997
1.3700097842
;
run;

My excel file attachment may explain better the desired end result for dataset C.

Dataset A:

Date	Hour	var_A
6/1/2018	100	1
6/1/2018	200	2
6/1/2018	300	3
6/1/2018	400	4
6/1/2018	500	5
6/1/2018	600	6
6/1/2018	700	7
6/1/2018	800	8
6/1/2018	900	9
6/1/2018	1000	10
6/1/2018	1100	11
6/1/2018	1200	12
6/1/2018	1300	13
6/1/2018	1400	14
6/1/2018	1500	15
6/1/2018	1600	16
6/1/2018	1700	17
6/1/2018	1800	18
6/1/2018	1900	19
6/1/2018	2000	20
6/1/2018	2100	21
6/1/2018	2200	22
6/1/2018	2300	23
6/1/2018	2400	24
6/4/2018	100	25
6/4/2018	200	26
6/4/2018	300	27
6/4/2018	400	28
6/4/2018	500	29
6/4/2018	600	30
6/4/2018	700	31
6/4/2018	800	32
6/4/2018	900	33
6/4/2018	1000	34
6/4/2018	1100	35
6/4/2018	1200	36
6/4/2018	1300	37
6/4/2018	1400	38
6/4/2018	1500	39
6/4/2018	1600	40
6/4/2018	1700	41
6/4/2018	1800	42
6/4/2018	1900	43
6/4/2018	2000	44
6/4/2018	2100	45
6/4/2018	2200	46
6/4/2018	2300	47
6/4/2018	2400	48
6/5/2018	100	49
6/5/2018	200	50
6/5/2018	300	51
6/5/2018	400	52
6/5/2018	500	53
6/5/2018	600	54
6/5/2018	700	55
6/5/2018	800	56
6/5/2018	900	57
6/5/2018	1000	58
6/5/2018	1100	59
6/5/2018	1200	60
6/5/2018	1300	61
6/5/2018	1400	62
6/5/2018	1500	63
6/5/2018	1600	64
6/5/2018	1700	65
6/5/2018	1800	66
6/5/2018	1900	67
6/5/2018	2000	68
6/5/2018	2100	69
6/5/2018	2200	70
6/5/2018	2300	71
6/5/2018	2400	72
6/6/2018	100	73
6/6/2018	200	74
6/6/2018	300	75
6/6/2018	400	76
6/6/2018	500	77
6/6/2018	600	78
6/6/2018	700	79
6/6/2018	800	80
6/6/2018	900	81
6/6/2018	1000	82
6/6/2018	1100	83
6/6/2018	1200	84
6/6/2018	1300	85
6/6/2018	1400	86
6/6/2018	1500	87
6/6/2018	1600	88
6/6/2018	1700	89
6/6/2018	1800	90
6/6/2018	1900	91
6/6/2018	2000	92
6/6/2018	2100	93
6/6/2018	2200	94
6/6/2018	2300	95
6/6/2018	2400	96

Dataset B:

Hour	var_B
100	2.1
200	3.1
300	4.1
400	5.1
500	6.1
600	7.1
700	8.1
800	9.1
900	10.1
1000	11.1
1100	12.1
1200	13.1
1300	14.1
1400	15.1
1500	16.1
1600	17.1
1700	18.1
1800	19.1
1900	20.1
2000	21.1
2100	22.1
2200	23.1
2300	24.1
2400	25.1

Dataset C (goal dataset):

Date	Hour	var_A	var_B
6/1/2018	100	1	2.1
6/1/2018	200	2	3.1
6/1/2018	300	3	4.1
6/1/2018	400	4	5.1
6/1/2018	500	5	6.1
6/1/2018	600	6	7.1
6/1/2018	700	7	8.1
6/1/2018	800	8	9.1
6/1/2018	900	9	10.1
6/1/2018	1000	10	11.1
6/1/2018	1100	11	12.1
6/1/2018	1200	12	13.1
6/1/2018	1300	13	14.1
6/1/2018	1400	14	15.1
6/1/2018	1500	15	16.1
6/1/2018	1600	16	17.1
6/1/2018	1700	17	18.1
6/1/2018	1800	18	19.1
6/1/2018	1900	19	20.1
6/1/2018	2000	20	21.1
6/1/2018	2100	21	22.1
6/1/2018	2200	22	23.1
6/1/2018	2300	23	24.1
6/1/2018	2400	24	25.1
6/4/2018	100	25	2.1
6/4/2018	200	26	3.1
6/4/2018	300	27	4.1
6/4/2018	400	28	5.1
6/4/2018	500	29	6.1
6/4/2018	600	30	7.1
6/4/2018	700	31	8.1
6/4/2018	800	32	9.1
6/4/2018	900	33	10.1
6/4/2018	1000	34	11.1
6/4/2018	1100	35	12.1
6/4/2018	1200	36	13.1
6/4/2018	1300	37	14.1
6/4/2018	1400	38	15.1
6/4/2018	1500	39	16.1
6/4/2018	1600	40	17.1
6/4/2018	1700	41	18.1
6/4/2018	1800	42	19.1
6/4/2018	1900	43	20.1
6/4/2018	2000	44	21.1
6/4/2018	2100	45	22.1
6/4/2018	2200	46	23.1
6/4/2018	2300	47	24.1
6/4/2018	2400	48	25.1
6/5/2018	100	49	2.1
6/5/2018	200	50	3.1
6/5/2018	300	51	4.1
6/5/2018	400	52	5.1
6/5/2018	500	53	6.1
6/5/2018	600	54	7.1
6/5/2018	700	55	8.1
6/5/2018	800	56	9.1
6/5/2018	900	57	10.1
6/5/2018	1000	58	11.1
6/5/2018	1100	59	12.1
6/5/2018	1200	60	13.1
6/5/2018	1300	61	14.1
6/5/2018	1400	62	15.1
6/5/2018	1500	63	16.1
6/5/2018	1600	64	17.1
6/5/2018	1700	65	18.1
6/5/2018	1800	66	19.1
6/5/2018	1900	67	20.1
6/5/2018	2000	68	21.1
6/5/2018	2100	69	22.1
6/5/2018	2200	70	23.1
6/5/2018	2300	71	24.1
6/5/2018	2400	72	25.1
6/6/2018	100	73	2.1
6/6/2018	200	74	3.1
6/6/2018	300	75	4.1
6/6/2018	400	76	5.1
6/6/2018	500	77	6.1
6/6/2018	600	78	7.1
6/6/2018	700	79	8.1
6/6/2018	800	80	9.1
6/6/2018	900	81	10.1
6/6/2018	1000	82	11.1
6/6/2018	1100	83	12.1
6/6/2018	1200	84	13.1
6/6/2018	1300	85	14.1
6/6/2018	1400	86	15.1
6/6/2018	1500	87	16.1
6/6/2018	1600	88	17.1
6/6/2018	1700	89	18.1
6/6/2018	1800	90	19.1
6/6/2018	1900	91	20.1
6/6/2018	2000	92	21.1
6/6/2018	2100	93	22.1
6/6/2018	2200	94	23.1
6/6/2018	2300	95	24.1
6/6/2018	2400	96	25.1

Re: Repeat datalines when creating a new variable in dataset

Reeza — Wed, 01 May 2019 22:09:35 GMT

Add an hour value into the data set so you can merge by group AND hour and it will replicate by default. This assumes you have a variable that identifies different groups already.

Sorry can't download or view attachments due to security restrictions so no idea what's in that file.

Re: Repeat datalines when creating a new variable in dataset

KALLEN — Wed, 01 May 2019 22:41:36 GMT

This was my solution:

proc sort data=dataset_a;
	by Hour;
run;

data dataset_c;
	merge dataset_a dataset_b;
	by hour;
run;

proc sort data=dataset_c;
	by Date;
run;

topic Re: Repeat datalines when creating a new variable in dataset in SAS Programming

Repeat datalines when creating a new variable in dataset

Re: Repeat datalines when creating a new variable in dataset

Re: Repeat datalines when creating a new variable in dataset