SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

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

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!

 


Accepted Solutions
Solution
‎03-07-2017 11:16 AM
Super User
Posts: 11,343

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

Posted in reply to Kristinos
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;

View solution in original post


All Replies
PROC Star
Posts: 1,167

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

Posted in reply to Kristinos

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

 

Tom

SAS Employee
Posts: 66

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

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.
Contributor
Posts: 25

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

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

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

Posted in reply to Kristinos
Contributor
Posts: 25

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

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

Contributor
Posts: 25

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

Posted in reply to Kristinos
I was not able to make sas understand that the first row was dates...
Super User
Super User
Posts: 7,942

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

[ Edited ]
Posted in reply to Kristinos

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;

 

Contributor
Posts: 25

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

I'm so sorry RW9! My brain is kind of chaotic today... Smiley Sad
Solution
‎03-07-2017 11:16 AM
Super User
Posts: 11,343

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

Posted in reply to Kristinos
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;
Contributor
Posts: 25

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

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

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

Posted in reply to Kristinos

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

SAS Employee
Posts: 66

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

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 296 views
  • 1 like
  • 5 in conversation