topic Re: I want only ONE date with observation in each row, but I have 200... in SAS Data Management

I want only ONE date with observation in each row, but I have 200...

Kristinos — Tue, 07 Mar 2017 15:10:28 GMT

I have a dataset in excel that looks like this:

ID	2016.09.13	2016.09.14	2016.09.15
5756	28.74	35.87	35.04
5830	26.65	25.51	29.78
5838	13.2	.	17.03

The first column is ID-number

The following columns are milk yield at a given dates.

I want to import the data to SAS (which is not a problem), but to be able to work with the dataset in SAS I need it to look like this:

ID	Date	Milk yield
5756	2016.09.13	28.74
5830	2016.09.13	26.65
5838	2016.09.13	13.2
5756	2016.09.14	35.87
5830	2016.09.14	25.51
5838	2016.09.14	.
5756	2016.09.15	35.04
5830	2016.09.15	29.78
5838	2016.09.15	17.03

Does anyone have a clue how to do that? I'm totaly blank!

Re: I want only ONE date with observation in each row, but I have 200...

TomKari — Tue, 07 Mar 2017 15:31:50 GMT

It should be pretty easy. But first, how are you importing it into SAS, and what does your imported SAS dataset look like?

Tom

Re: I want only ONE date with observation in each row, but I have 200...

MelodieRush — Tue, 07 Mar 2017 15:33:45 GMT

Are you using SAS programming or an interface like Enterprise Guide or SAS Studio. This is very easy to do using PROC Transpose once you have the data imported into SAS.

Re: I want only ONE date with observation in each row, but I have 200...

ballardw — Tue, 07 Mar 2017 15:34:55 GMT

Proc transpose is going to be the likely tool. If you want the "dates" to be actual SAS dates usable in interval calculations and such you will need to do some additional processing.

If you provide a few rows of data from the SAS data set after importing we can show some example code. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against. For your data probably only 4 or 5 rows of data should be sufficient.

Re: I want only ONE date with observation in each row, but I have 200...

Kristinos — Tue, 07 Mar 2017 15:36:53 GMT

I'm importing with proc import:
Title ;
PROC Import OUT= work.Dataset_Milkyield
DATAFILE= "Y:\\Dataset_milkyield.xlsx"
DBMS=xlsx REPLACE;
Getnames=YES;
RUN;

Re: I want only ONE date with observation in each row, but I have 200...

MelodieRush — Tue, 07 Mar 2017 15:49:39 GMT

Here's the code from SAS Enterprise Guide - Stack Task. As a previous poster indicated you will need to do some coding to use the dates as actual date values.

PROC TRANSPOSE DATA = WORK.TMP1TempTableWork
OUT=WORK.TRNSStackColumns(LABEL="Stacked WORK.MILKSAMPLE")
NAME=Date
LABEL=Id
;
BY id;
ID _EG_IDCOL_;
VAR "2016.09.13"n "2016.09.14"n "2016.09.15"n;

RUN;

PROC DATASETS LIB=WORK NOLIST;
MODIFY TRNSStackColumns;
LABEL MilkYield = "The values of the columns being stacked.";
LABEL Date = "The name of the column from which the value came.";
LABEL Id = "The label of the column from which the value came.";
RUN;

Re: I want only ONE date with observation in each row, but I have 200...

RW9 — Tue, 07 Mar 2017 15:51:22 GMT

Show us the data as it is after import, follow this:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: I want only ONE date with observation in each row, but I have 200...

Kristinos — Tue, 07 Mar 2017 15:59:31 GMT

The dataset looks like this after import:

Obs	ID	_2016_12_21	_2016_12_22	_2016_12_23	_2016_12_24	_2016_12_25	_2016_12_26	_2016_12_27	_2016_12_28	_2016_12_29	_2016_12_30	_2016_12_31	_2017_01_01	_2017_01_02	_2017_01_03	_2017_01_04	_2017_01_05	_2017_01_06	_2017_01_07	_2017_01_08	_2017_01_09	_2017_01_10	_2017_01_11	_2017_01_12	_2017_01_13	_2017_01_14	_2017_01_15	_2017_01_16	_2017_01_17	_2017_01_18	_2017_01_19	_2017_01_20	_2017_01_21	_2017_01_22	_2017_01_23	_2017_01_24	_2017_01_25	_2017_01_26	_2017_01_27	_2017_01_28	_2017_01_29	_2017_01_30	_2017_01_31	_2017_02_01	_2017_02_02	_2017_02_03	_2017_02_04	_2017_02_05	_2017_02_06	_2017_02_07	_2017_02_08	_2017_02_09	_2017_02_10	_2017_02_11	_2017_02_12	_2017_02_13	_2017_02_14	_2017_02_15	_2017_02_16	_2017_02_17	_2017_02_18	_2017_02_19	_2017_02_20
1	5575	32.77	41.7	28.12	38.52	45.02	33.65	32.21	38.85	40.52	29.31	38.93	33.1	33.94	32.59	36.65	33.75	33.76	34.04	34.02	19.08	35.1	36.95	26.12	48.02	16.14	36.57	42.76	28.95	36.67	18.09	29.66	35.63	34.75	41.29	33.85	36.25	21.24	41.29	41.73	20.72	39.71	20.71	40.59	31.64	39.44	33.12	27.28	42.64	35.73	20.55	41.71	41.07	21.97	23.64	42.19	21.19	42.83	38.4	20.13	39.33	31.5	36.44
2	5613	46.34	26.24	40.32	51.1	36.91	26.19	27.62	28.43	49.99	38.26	40.39	29.63	43.46	28.14	41.61	28.75	43.99	27.92	39.37	26.13	41.68	40.61	38.62	36.18	34.72	35.01	32.26	31.18	34.52	41.97	34.22	23.79	46.6	29.7	35.87	34.54	35.36	34.03	34.19	35.67	32.59	37.87	35.54	38.99	37.66	34.49	30.59	29.8	34.18	38.05	25.63	31.25	33.02	36.65	32.94	22.22	40.05	31.92	37.63	29.51	31.74	31.37
3	5651	29.75	36.73	54.97	35.04	44.19	34.97	22.21	36.97	34.95	45.28	40.22	37.15	36.43	46.33	27.89	35.41	43.52	36.52	34.15	39.46	23.78	48.23	33.41	39.23	37.88	25.65	45.69	29.11	35.66	36.76	37.09	26.23	32.73	35.34	37.5	33.44	26.54	37.65	31.71	41.41	33.82	22.88	40.01	30.7	40.43	28.24	38.1	29.59	31.33	38.35	34.86	29.66	29.65	35.49	36.93	27.5	37.58	32.34	28.79	32.26	33.98	34.24
4	5714	45.93	44.42	35.13	32.68	41.83	41.33	41.98	39.49	41.33	34.41	40	28.07	43.36	36.83	39.37	35.39	21.8	46.95	30.47	41.21	25.66	50.47	29.39	37.69	26.55	45.23	38.51	29.32	38.55	43.1	29.39	30.76	39.19	39.96	34.09	34.85	25.31	40.84	41.72	31.23	37.1	36.68	28.29	45.14	27.59	36.41	40.22	24.59	44.1	27.53	40.25	29.6	41.65	28.61	43.85	36.88	38.9	28.96	40.36	37.45	36.54	27.16
5	5725	19.67	45.94	31.02	34.2	32.03	45.44	15.83	34.11	33.42	28.87	33.11	33.21	31.7	32.14	30.39	33.02	31.94	33.52	25.31	33.06	20.56	41.44	28.85	23.69	28.44	34.46	23.71	28.75	31.26	15.65	33.35	24.34	18.02	27.56	24.67	26.9	14.41	32.43	21.77	26.16	29.05	35.7	23	25.97	26.2	12.53	32.44	15.06	33.83	13.76	26.54	26.42	17.18	19.52	32.38	14.1	31.15	24.45	12.9	30.1	24.8	24.08

It has 200 columns with dates and 140 rows with individuals

Re: I want only ONE date with observation in each row, but I have 200...

Kristinos — Tue, 07 Mar 2017 16:00:33 GMT

I was not able to make sas understand that the first row was dates...

Re: I want only ONE date with observation in each row, but I have 200...

RW9 — Tue, 07 Mar 2017 16:02:49 GMT

Wow, you totally ignored my post. As I am not typing that data in here is an example which is untested;

proc transpose data=have out=want;  
  by id;
  var _201:;
run;

And then to make a column of dates:

data want (drop=_name_);
  set want;
  new_date=input(compress(_name_,"_"),yymmdd10.);
run;

Re: I want only ONE date with observation in each row, but I have 200...

Kristinos — Tue, 07 Mar 2017 16:06:57 GMT

I'm so sorry RW9! My brain is kind of chaotic today... 😞

Re: I want only ONE date with observation in each row, but I have 200...

ballardw — Tue, 07 Mar 2017 16:13:42 GMT

data stupid;
 input ID _2016_12_21 _2016_12_22 _2016_12_23 _2016_12_24 _2016_12_25 _2016_12_26 _2016_12_27 _2016_12_28 _2016_12_29 _2016_12_30 _2016_12_31 _2017_01_01 _2017_01_02 _2017_01_03 _2017_01_04 _2017_01_05 _2017_01_06 _2017_01_07 _2017_01_08 _2017_01_09 _2017_01_10 _2017_01_11 _2017_01_12 _2017_01_13 _2017_01_14 _2017_01_15 _2017_01_16 _2017_01_17 _2017_01_18 _2017_01_19 _2017_01_20 _2017_01_21 _2017_01_22 _2017_01_23 _2017_01_24 _2017_01_25 _2017_01_26 _2017_01_27 _2017_01_28 _2017_01_29 _2017_01_30 _2017_01_31 _2017_02_01 _2017_02_02 _2017_02_03 _2017_02_04 _2017_02_05 _2017_02_06 _2017_02_07 _2017_02_08 _2017_02_09 _2017_02_10 _2017_02_11 _2017_02_12 _2017_02_13 _2017_02_14 _2017_02_15 _2017_02_16 _2017_02_17 _2017_02_18 _2017_02_19 _2017_02_20 ;
datalines;
5575 32.77 41.7 28.12 38.52 45.02 33.65 32.21 38.85 40.52 29.31 38.93 33.1 33.94 32.59 36.65 33.75 33.76 34.04 34.02 19.08 35.1 36.95 26.12 48.02 16.14 36.57 42.76 28.95 36.67 18.09 29.66 35.63 34.75 41.29 33.85 36.25 21.24 41.29 41.73 20.72 39.71 20.71 40.59 31.64 39.44 33.12 27.28 42.64 35.73 20.55 41.71 41.07 21.97 23.64 42.19 21.19 42.83 38.4 20.13 39.33 31.5 36.44 
5613 46.34 26.24 40.32 51.1 36.91 26.19 27.62 28.43 49.99 38.26 40.39 29.63 43.46 28.14 41.61 28.75 43.99 27.92 39.37 26.13 41.68 40.61 38.62 36.18 34.72 35.01 32.26 31.18 34.52 41.97 34.22 23.79 46.6 29.7 35.87 34.54 35.36 34.03 34.19 35.67 32.59 37.87 35.54 38.99 37.66 34.49 30.59 29.8 34.18 38.05 25.63 31.25 33.02 36.65 32.94 22.22 40.05 31.92 37.63 29.51 31.74 31.37 
5651 29.75 36.73 54.97 35.04 44.19 34.97 22.21 36.97 34.95 45.28 40.22 37.15 36.43 46.33 27.89 35.41 43.52 36.52 34.15 39.46 23.78 48.23 33.41 39.23 37.88 25.65 45.69 29.11 35.66 36.76 37.09 26.23 32.73 35.34 37.5 33.44 26.54 37.65 31.71 41.41 33.82 22.88 40.01 30.7 40.43 28.24 38.1 29.59 31.33 38.35 34.86 29.66 29.65 35.49 36.93 27.5 37.58 32.34 28.79 32.26 33.98 34.24 
5714 45.93 44.42 35.13 32.68 41.83 41.33 41.98 39.49 41.33 34.41 40 28.07 43.36 36.83 39.37 35.39 21.8 46.95 30.47 41.21 25.66 50.47 29.39 37.69 26.55 45.23 38.51 29.32 38.55 43.1 29.39 30.76 39.19 39.96 34.09 34.85 25.31 40.84 41.72 31.23 37.1 36.68 28.29 45.14 27.59 36.41 40.22 24.59 44.1 27.53 40.25 29.6 41.65 28.61 43.85 36.88 38.9 28.96 40.36 37.45 36.54 27.16 
5725 19.67 45.94 31.02 34.2 32.03 45.44 15.83 34.11 33.42 28.87 33.11 33.21 31.7 32.14 30.39 33.02 31.94 33.52 25.31 33.06 20.56 41.44 28.85 23.69 28.44 34.46 23.71 28.75 31.26 15.65 33.35 24.34 18.02 27.56 24.67 26.9 14.41 32.43 21.77 26.16 29.05 35.7 23 25.97 26.2 12.53 32.44 15.06 33.83 13.76 26.54 26.42 17.18 19.52 32.38 14.1 31.15 24.45 12.9 30.1 24.8 24.08 
;
run;

proc transpose data=stupid out=temp label=id;
   by id;
   var _2: ;
run;

data want;
   set temp (rename=(col1=MilkYield));
   date = input(substr(_name_,2),yymmdd10.);
   format date yymmddp10.;
   drop _name_;
run;

Re: I want only ONE date with observation in each row, but I have 200...

Kristinos — Tue, 07 Mar 2017 16:19:19 GMT

Thank you so much for the help! Both RW9 and ballardw! This seems to work very fine!
And thanks for keeping up with my slow brain...