BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kristinos
Calcite | Level 5

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User
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

12 REPLIES 12
TomKari
Onyx | Level 15

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

 

Tom

MelodieRush
SAS Employee
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.

Catch the SAS Global Forum keynotes, announcements, and tech content!
sasglobalforum.com | #SASGF



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

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

Kristinos
Calcite | Level 5
I was not able to make sas understand that the first row was dates...
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

Kristinos
Calcite | Level 5
I'm so sorry RW9! My brain is kind of chaotic today... 😞
ballardw
Super User
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;
Kristinos
Calcite | Level 5
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...
ballardw
Super User

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.

MelodieRush
SAS Employee

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;

Catch the SAS Global Forum keynotes, announcements, and tech content!
sasglobalforum.com | #SASGF



sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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