creating data with Delimited Files

Mariloud · Posted 02-21-2019 10:19 PM

Hi everyone,

I am trying to create a data set with the file below. it's a record of snow fall in specific areas. Here are the following variables GHCNID, StationName, County, Elevation, Latitude, Longitude, and feb1 feb 2......feb 20 which are the day there was snow. I want to create another variable date to record the data from feb1 to feb 20. Not sure how to proceed. I used the dlm='09'x dsd to read the file but not sure how to read the day from feb1-feb20 (which represent February 1 to February 20). Do I need to transpose the data? or do other modification use other type of input.

Appreciate any help.

PGStats · Posted 02-21-2019 11:00 PM

It doesn't look like dlm='09'x ('09'x is the TAB character) would be appropriate for this file. The default delimiter (the comma) should work better with DSD.

That said, you can use an array to transform your data into long form. Something like

array f feb_1-feb_20;

do febDay = 1 to 20;
    snow = f{febDay};
    output;
    end;
    
drop feb_1-feb_20;

PG

Mariloud · Posted 02-23-2019 10:21 PM

Thank you for responding. Using the DSD actually worked.

Kurt_Bremser · Posted 02-22-2019 07:00 AM

Help us to help you by providing your input in usable form. Copy/paste a few lines of the text file into a window opened with the {i} button.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Mariloud · Posted 02-23-2019 10:18 PM

Sorry, here is the complete data I am trying to work with. Its a record of snow fall in different county from February 1 to February 20 with them computed as variable. what I am trying to do is to create a variable that will record the snow fall from February 1- February 20, basically making them observation instead of variables.

"GHCN ID","Station Name",County,Elevation,Latitude,Longitude,"Feb 1","Feb 2","Feb 3","Feb 4","Feb 5","Feb 6","Feb 7","Feb 8","Feb 9","Feb 10","Feb 11","Feb 12","Feb 13","Feb 14","Feb 15","Feb 16","Feb 17","Feb 18","Feb 19","Feb 20"
USW00013701,"ABERDEEN PHILLIPS FLD","HARFORD",57,39.47,-76.17,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,M,M,2.5,4.2,0.0,0.0,0.0,M,M,M,M,M
US1MDFR0023,"ADAMSTOWN 0.7 ESE","FREDERICK",317,39.31,-77.46,0.0,1.9,M,M,0.0,M,M,M,M,M,M,M,M,M,0.0,M,0.0,M,M,M
US1MDAA0070,"ANNAPOLIS 3.2 NW","ANNE ARUNDEL",117,39.00,-76.55,M,0.0,0.0,0.0,0.0,0.0,M,M,0.0,M,M,M,M,0.0,0.0,0.0,0.0,M,0.0,M
USW00093721,"BALTIMORE-WASHINGTON INTERNATIONA","ANNE ARUNDEL",156,39.17,-76.68,1.1,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.5,T,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,M
US1MDHR0011,"BEL AIR 1.7 W","HARFORD",433,39.54,-76.38,M,1.5,M,M,M,M,M,M,M,M,2.0,M,M,M,M,M,M,M,T,M
USC00180700,"BELTSVILLE","PRINCE GEORGE'S",145,39.03,-76.93,0.0,1.3,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.2,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.9
US1MDPG0082,"BELTSVILLE 1.4 NNW","PRINCE GEORGE'S",203,39.06,-76.93,0.0,1.4,0.0,0.0,0.0,M,M,M,M,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,0.1
US1MDWR0009,"BERLIN 0.7 SW","WORCESTER",38,38.32,-75.23,0.0,1.5,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,0.0,M,M,M,0.0,M,M,M,M
US1MDWR0022,"BERLIN 5.9 NNE","WORCESTER",17,38.40,-75.16,M,M,0.0,0.0,0.0,0.0,M,0.0,0.0,M,M,M,M,M,M,M,M,M,M,M
US1MDWR0019,"BERLIN 6.5 SSE","WORCESTER",4,38.24,-75.18,0.0,0.6,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,M,M,M,0.0,M,M,M,0.1
US1MDMG0066,"BETHESDA 2.0 WNW","MONTGOMERY",224,39.00,-77.16,0.0,0.0,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,T,M,M,0.0,0.0,0.0,0.0,M,0.0,0.6
US1MDAA0001,"BIRDSVILLE 0.2 WSW","ANNE ARUNDEL",151,38.90,-76.60,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,T
US1MDWR0004,"BISHOPVILLE 3.1 E","WORCESTER",3,38.45,-75.13,M,2.5,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M
US1MDPG0046,"BOWIE 2.4 NNE","PRINCE GEORGE'S",101,38.99,-76.73,M,M,M,M,M,M,M,M,M,0.0,M,M,M,0.0,M,M,M,M,M,M
US1MDPG0012,"BOWIE 2.8 WNW","PRINCE GEORGE'S",194,38.98,-76.78,M,1.3,0.0,0.0,0.0,0.0,M,0.0,M,0.0,1.0,M,M,M,M,M,M,M,M,M
US1MDPG0050,"BOWIE 3.9 S","PRINCE GEORGE'S",127,38.90,-76.73,M,1.0,0.0,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M
US1MDPG0037,"BRANDYWINE 6.7 ESE","PRINCE GEORGE'S",158,38.66,-76.74,T,0.6,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,0.0,0.8
US1MDDR0007,"CAMBRIDGE 1.4 WNW","DORCHESTER",11,38.57,-76.10,0.0,M,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,M,M
US1MDBL0039,"CATONSVILLE 1.2 NW","BALTIMORE",464,39.28,-76.76,0.0,1.3,0.0,0.0,0.0,0.0,M,M,M,0.0,M,M,M,0.0,0.0,0.0,0.0,M,M,M
US1MDCC0015,"CHARLESTOWN 0.4 WSW","CECIL",0,39.57,-75.99,0.0,1.1,0.0,0.0,0.0,0.0,M,M,0.0,0.0,3.0,1.0,M,0.0,0.0,0.0,0.0,M,0.0,0.0
US1MDCV0020,"CHESAPEAKE BEACH 4.3 SSE","CALVERT",90,38.63,-76.52,M,M,M,M,M,M,M,0.0,0.0,0.0,M,M,M,M,M,M,M,M,0.0,1.0
US1MDMG0029,"CLARKSBURG 2.1 NE","MONTGOMERY",682,39.25,-77.24,2.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.1,T,T,0.0,0.0,0.0,M,M,M,M,M
US1MDMG0018,"COLESVILLE 1.7 W","MONTGOMERY",361,39.09,-77.04,0.0,1.6,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.4,0.0,0.0,M,M,M,M,0.0,M,0.5
US1MDHW0018,"COLUMBIA 2.3 N","HOWARD",407,39.24,-76.86,0.0,1.7,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.7,T,T,0.0,0.0,0.0,0.0,0.0,0.0,0.0
USC00182060,"CONOWINGO DAM","HARFORD",40,39.66,-76.18,0.0,M,0.0,0.0,0.0,0.0,0.0,0.0,M,M,2.0,M,0.0,0.0,0.0,0.0,0.0,0.0,0.0,M
US1MDAL0002,"CRESAPTOWN-BEL AIR 0.9 SSE","ALLEGANY",755,39.58,-78.85,1.6,5.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.5,T,0.0,0.0,0.0,0.0,0.0,M,2.1
US1MDAA0071,"CROFTON 1.1 SSE","ANNE ARUNDEL",148,39.00,-76.68,0.0,1.2,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.6,0.0,0.0,0.0,0.0,0.0,0.0,T,M,M
USC00182282,"CUMBERLAND 2","ALLEGANY",730,39.64,-78.76,1.6,3.4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.2,0.7,0.0,0.0,0.0,0.0,0.0,0.0,0.0,2.0
USC00182336,"DAMASCUS 3 SSW","MONTGOMERY",700,39.26,-77.23,0.0,2.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.2
US1MDSS0012,"DEAL ISLAND 0.5 SSW","SOMERSET",5,38.15,-75.95,0.0,M,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,M,0.0,0.0,0.0,M,M,0.0,M
US1MDAA0043,"DEALE 0.9 SE","ANNE ARUNDEL",7,38.78,-76.54,T,1.3,0.0,0.0,0.0,0.0,M,M,M,0.0,T,M,M,0.0,0.0,0.0,0.0,M,0.0,T
US1MDWC0004,"DELMAR 0.2 NE","WICOMICO",49,38.45,-75.57,0.0,M,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,M,0.0,M,0.0,0.0
US1MDCL0010,"DENTON 5.8 WSW","CAROLINE",39,38.86,-75.93,T,2.3,0.0,0.0,0.0,M,M,M,0.0,0.0,M,M,M,0.0,M,0.0,0.0,M,0.0,M
US1MDMG0103,"DICKERSON 6.6 SW","MONTGOMERY",228,39.15,-77.51,0.0,1.5,0.0,0.0,M,0.0,M,M,0.0,0.0,0.1,M,M,0.0,0.0,0.0,0.0,M,0.0,1.8
US1MDDR0010,"EAST NEW MARKET 0.0 WNW","DORCHESTER",43,38.60,-75.92,M,M,M,M,M,M,M,M,M,M,T,M,M,M,M,M,M,M,M,M
US1MDTB0011,"EASTON 0.7 NNW","TALBOT",33,38.78,-76.07,T,2.1,0.0,0.0,0.0,0.0,0.0,0.0,M,0.0,T,0.0,0.0,0.0,M,M,M,M,M,M
US1MDTB0008,"EASTON 1.2 SSW","TALBOT",13,38.76,-76.08,0.0,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M
US1MDTB0005,"EASTON 2.4 SE","TALBOT",56,38.75,-76.04,0.0,2.1,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,M,M,M,M,M,M,M,M
US1MDCR0019,"ELDERSBURG 0.9 E","CARROLL",602,39.40,-76.93,0.0,1.3,0.0,0.0,M,M,M,0.0,0.0,0.0,0.9,0.7,0.0,0.0,0.0,M,M,0.0,0.0,0.0
US1MDHW0003,"ELKRIDGE 1.8 W","HOWARD",341,39.20,-76.78,0.0,1.8,0.0,0.0,0.0,0.0,M,M,0.0,0.0,0.8,T,M,0.0,0.0,0.0,0.0,M,0.0,0.0
US1MDCC0006,"ELKTON 1.3 NW","CECIL",49,39.62,-75.84,0.5,0.5,M,M,M,M,M,0.0,M,M,3.3,M,M,M,M,M,M,M,M,M
US1MDCC0016,"ELKTON 4.8 NW","CECIL",250,39.65,-75.89,M,1.5,M,M,M,M,M,M,M,M,2.1,2.1,M,M,M,M,M,M,M,M
US1MDCC0010,"ELKTON 7.0 NNW","CECIL",347,39.70,-75.87,0.0,M,0.0,0.0,0.0,0.0,M,M,M,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,0.0
US1MDHW0013,"ELLICOTT CITY 1.3 WSW","HOWARD",413,39.27,-76.86,M,M,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,M,0.0,M,0.0,0.0,M,0.0,0.1
USC00182906,"EMMITSBURG 2 SE","FREDERICK",403,39.68,-77.28,0.0,3.5,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.5,1.5,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
US1MDHR0015,"FOREST HILL 1.7 SSE","HARFORD",472,39.57,-76.37,M,M,M,M,M,M,M,M,M,M,2.2,M,M,0.0,0.0,0.0,0.0,M,M,M
US1MDPG0049,"FORT WASHINGTON 1.6 WSW","PRINCE GEORGE'S",37,38.73,-77.03,0.2,0.3,0.0,0.0,0.0,0.0,M,M,0.0,0.0,0.1,M,M,M,0.0,0.0,0.0,M,M,0.6
USC00183415,"FROSTBURG 2","ALLEGANY",2169,39.66,-78.94,1.9,4.2,0.0,0.0,0.0,0.0,0.0,M,M,M,1.9,0.8,1.1,0.0,0.0,0.0,0.0,0.0,0.0,2.5
US1MDWC0022,"FRUITLAND 1.8 ENE","WICOMICO",24,38.33,-75.59,0.0,0.0,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,M,0.0
US1MDMG0122,"GAITHERSBURG 2.7 E","MONTGOMERY",497,39.14,-77.17,0.0,M,M,0.0,0.0,M,M,M,M,M,0.5,M,M,0.0,M,M,M,M,M,M
US1MDMG0063,"GAITHERSBURG 3.1 NE","MONTGOMERY",449,39.18,-77.18,M,1.3,M,0.0,0.0,M,M,M,M,M,0.5,M,M,M,M,M,M,M,T,0.7
US1MDMG0118,"GAITHERSBURG 6.8 N","MONTGOMERY",575,39.24,-77.20,0.0,1.5,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.5,T,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
US1MDCR0021,"GAMBER 0.8 W","CARROLL",656,39.47,-76.95,T,1.2,M,M,M,M,M,M,M,M,M,1.2,M,M,M,M,M,M,T,M
US1MDMG0054,"GERMANTOWN 4.6 NNE","MONTGOMERY",640,39.24,-77.23,0.0,1.6,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,0.0
US1MDPG0084,"GREENBELT 0.4 NW","PRINCE GEORGE'S",165,39.00,-76.89,0.0,M,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,M,2.6
US1MDPG0040,"GREENBELT 1.0 N","PRINCE GEORGE'S",207,39.01,-76.89,M,1.2,M,M,M,M,M,M,M,M,0.5,M,M,M,M,M,M,M,M,0.2
US1MDPG0047,"GREENBELT 2.2 E","PRINCE GEORGE'S",214,39.00,-76.84,0.9,0.2,0.0,0.0,M,M,M,M,M,0.0,0.3,M,M,M,M,M,M,M,M,M
US1MDCL0009,"GREENSBORO 1.4 ENE","CAROLINE",20,38.99,-75.78,0.0,2.7,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,0.6,M,M,0.0,M,0.0,0.0,M,0.0,0.0
USC00181790,"HAGERSTOWN 1 E","WASHINGTON",532,39.64,-77.70,3.4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.4,1.3,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,M
US1MDWH0003,"HAGERSTOWN 1.0 ENE","WASHINGTON",525,39.64,-77.70,0.0,3.4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.6,1.1,T,T,0.0,0.0,0.0,0.0,T,T
US1MDBC0001,"HAMILTON 0.3 NE","BALTIMORE (CITY)",308,39.36,-76.55,0.0,1.3,0.0,0.0,M,0.0,M,M,M,0.0,0.5,1.0,M,0.0,0.0,0.0,M,M,M,M
US1MDWH0011,"HANCOCK 0.8 ESE","WASHINGTON",554,39.70,-78.16,M,M,M,M,M,M,M,M,M,M,1.0,0.5,M,M,M,M,M,M,M,0.6
US1MDHR0004,"HAVRE DE GRACE 4.0 WNW","HARFORD",410,39.57,-76.17,0.0,M,0.0,0.0,0.0,0.0,M,M,M,0.0,4.0,M,0.0,0.0,0.0,0.0,M,M,M,M
US1MDCV0017,"HUNTINGTOWN 2.9 NNW","CALVERT",113,38.65,-76.64,0.1,1.1,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,T,M,M,0.0,0.0,0.0,0.0,M,0.0,0.4
US1MDPG0079,"HYATTSVILLE 0.7 N","PRINCE GEORGE'S",86,38.97,-76.95,0.0,M,M,M,M,M,M,M,0.0,M,M,M,M,0.0,0.0,0.0,0.0,M,0.0,0.0
US1MDBL0015,"JACKSONVILLE 1.0 SW","BALTIMORE",502,39.51,-76.57,0.0,1.8,0.0,0.0,0.0,0.0,M,M,M,M,M,M,M,M,M,M,0.0,M,0.0,0.0
US1MDBL0013,"JACKSONVILLE 1.5 NE","BALTIMORE",489,39.54,-76.54,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,2.5,2.5,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
US1MDFR0024,"JEFFERSON 1.6 SSE","FREDERICK",844,39.34,-77.52,M,M,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,M
US1MDHW0007,"JESSUP 3.0 W","HOWARD",318,39.15,-76.83,1.3,0.0,0.0,0.0,M,M,0.0,M,0.0,0.0,T,M,M,0.0,0.0,0.0,M,M,0.0,M
US1MDMG0040,"KENSINGTON 0.1 W","MONTGOMERY",302,39.03,-77.07,0.0,1.5,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,M
US1MDBL0028,"KINGSVILLE 1.2 E","BALTIMORE",202,39.45,-76.39,0.0,1.6,M,0.0,0.0,0.0,0.0,0.0,0.0,0.0,2.3,2.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
US1MDHR0006,"KINGSVILLE 2.5 NNE","HARFORD",407,39.48,-76.39,0.0,1.2,0.0,0.0,M,0.0,M,M,M,0.0,2.5,2.7,M,0.0,0.0,0.0,0.0,M,M,M
US1MDCH0036,"LA PLATA 5.8 SE","CHARLES",144,38.47,-76.91,0.0,T,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,M
US1MDHW0038,"LAUREL 1.3 NNE","HOWARD",218,39.11,-76.85,0.0,1.4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.5,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
US1MDPG0072,"LAUREL 2.5 WNW","PRINCE GEORGE'S",371,39.11,-76.90,M,M,0.0,0.0,0.0,0.0,M,M,M,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,1.5
US1MDMG0076,"LAYTONSVILLE 4.6 NNW","MONTGOMERY",593,39.26,-77.19,M,2.0,0.0,0.0,0.0,0.0,M,M,M,M,M,M,M,0.0,M,M,M,M,M,M
US1MDSM0003,"LEONARDTOWN 0.6 NE","ST. MARY'S",108,38.31,-76.63,T,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,0.0,0.0,1.5
US1MDAA0035,"LINTHICUM 0.4 ENE","ANNE ARUNDEL",174,39.21,-76.66,M,1.5,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M
US1MDBL0009,"LONG GREEN 0.6 SW","BALTIMORE",459,39.47,-76.53,0.0,2.2,0.0,0.0,0.0,0.0,M,M,0.0,0.0,2.4,3.0,M,T,0.0,0.0,0.0,M,0.0,0.0
US1MDCV0001,"MARLTON 6.0 E","CALVERT",39,38.74,-76.68,M,1.6,M,M,M,M,M,M,M,M,0.5,M,M,M,M,M,M,M,M,0.6
US1MDHW0023,"MARRIOTTSVILLE 0.6 SE","HOWARD",486,39.34,-76.89,0.0,1.3,0.0,0.0,0.0,0.0,M,M,M,M,0.7,M,M,M,M,M,M,M,M,M
US1MDGR0004,"MC HENRY 4.8 SSE","GARRETT",2496,39.49,-79.32,5.0,7.0,0.0,0.0,M,M,M,M,M,T,1.0,0.5,0.5,0.5,0.0,0.0,0.0,M,0.5,4.0
USC00185865,"MECHANICSVILLE 5 NE","ST. MARY'S",100,38.49,-76.73,0.0,0.3,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,T,M,M,0.0,0.0,0.0,0.0,M,0.0,T
US1MDFR0020,"MIDDLETOWN 1.8 NNE","FREDERICK",780,39.46,-77.53,0.0,M,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,M
USC00185934,"MILLERS 4 NE","CARROLL",860,39.72,-76.80,1.8,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,3.6,1.1,0.0,0.0,0.0,0.0,M,0.0,0.0,M
US1MDPG0086,"MITCHELLVILLE 0.5 SW","PRINCE GEORGE'S",167,38.92,-76.83,T,0.9,0.0,M,M,0.0,M,M,M,M,M,M,M,M,M,M,0.0,M,M,0.8
US1MDCR0003,"MOUNT AIRY 0.2 SE","CARROLL",830,39.37,-77.16,M,M,M,M,M,M,M,M,M,M,1.0,M,M,M,M,M,M,M,M,M
US1MDBC0003,"MOUNT WASHINGTON 0.5 N","BALTIMORE (CITY)",385,39.37,-76.66,M,1.2,M,M,M,M,M,M,M,M,1.2,0.6,T,M,0.0,0.0,0.0,M,M,M
US1MDGR0009,"MOUNTAIN LAKE PARK 0.4 E","GARRETT",2450,39.40,-79.37,4.0,8.5,0.0,0.0,0.0,0.0,0.0,0.0,T,T,1.0,0.1,0.5,0.5,0.0,0.0,0.0,0.0,1.0,0.5
US1MDCH0038,"NANJEMOY 5.3 S","CHARLES",31,38.38,-77.20,0.0,0.0,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,1.3
US1MDFR0012,"NEW MARKET 2.6 NNW","FREDERICK",370,39.42,-77.29,0.0,1.6,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,0.4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
US1MDMG0005,"NORBECK 1.1 ESE","MONTGOMERY",440,39.10,-77.06,0.0,1.5,0.0,0.0,0.0,0.0,M,M,M,0.0,0.7,0.0,0.0,0.0,0.0,0.0,0.0,M,0.0,0.5
US1MDHR0023,"NORRISVILLE 0.6 WSW","HARFORD",740,39.70,-76.54,0.1,2.6,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,2.6,2.4,T,0.0,0.0,0.0,0.0,T,0.0,T
US1MDCV0021,"NORTH BEACH 1.8 WNW","CALVERT",0,38.72,-76.57,M,M,M,M,M,M,M,M,M,M,M,M,M,0.0,0.0,0.0,0.0,M,M,0.1
US1MDHW0021,"NORTH LAUREL 1.5 ESE","HOWARD",282,39.13,-76.84,0.0,1.3,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,0.5,M,M,0.0,0.0,0.0,0.0,M,M,M
USC00186620,"OAKLAND 1 SE","GARRETT",2419,39.41,-79.40,5.0,4.5,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.5,0.0,0.5,0.0,0.0,0.0,0.0,0.0,M,M
USW00093786,"OCEAN CITY MUNICIPAL AP","WORCESTER",12,38.31,-75.12,M,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,M,0.0,0.0,0.0,0.0,M,0.0,M
US1MDWR0021,"OCEAN PINES 0.9 SSW","WORCESTER",8,38.37,-75.16,0.0,2.0,0.0,0.0,0.0,0.0,M,M,M,M,M,M,M,M,M,M,M,M,M,M
US1MDWR0017,"OCEAN PINES 1.5 SE","WORCESTER",5,38.37,-75.13,M,M,M,M,M,0.0,M,0.0,0.0,0.0,M,M,M,M,M,0.0,0.0,M,M,0.0
US1MDAA0065,"ODENTON 1.0 N","ANNE ARUNDEL",219,39.08,-76.69,0.0,1.5,0.0,0.0,0.0,0.0,M,M,M,0.0,0.6,0.0,0.0,0.0,M,0.0,0.0,0.0,0.0,T
US1MDMG0111,"OLNEY 1.0 SE","MONTGOMERY",536,39.14,-77.07,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,T,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,M
US1MDMG0078,"OLNEY 1.4 ENE","MONTGOMERY",475,39.16,-77.06,0.0,1.8,0.0,M,M,M,M,M,M,M,0.3,M,M,M,M,M,M,M,M,M
USC00186800,"OXON HILL","PRINCE GEORGE'S",190,38.80,-76.99,0.3,0.8,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.3
US1MDSM0008,"PARK HALL 0.2 SW","ST. MARY'S",75,38.22,-76.44,M,0.5,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,1.1
US1MDWC0009,"PARSONSBURG 1.6 WNW","WICOMICO",69,38.39,-75.50,0.0,1.0,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,M,M,0.0,0.0,0.0,M,0.0,0.0
US1MDAA0039,"PASADENA 2.6 ESE","ANNE ARUNDEL",35,39.09,-76.51,0.0,1.4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.8,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.7
US1MDWC0020,"PITTSVILLE 0.2 ESE","WICOMICO",48,38.39,-75.41,0.0,1.5,M,M,M,M,M,0.0,0.0,0.0,M,M,M,M,0.0,0.0,0.0,M,0.0,M
US1MDMG0065,"POOLESVILLE 0.4 SE","MONTGOMERY",372,39.14,-77.40,M,0.8,0.0,0.0,0.0,0.0,0.0,M,0.0,0.0,0.4,M,M,0.0,0.0,0.0,0.0,M,0.0,4.0
US1MDMG0120,"POOLESVILLE 5.4 W","MONTGOMERY",231,39.15,-77.51,M,M,M,M,M,M,M,M,0.0,0.0,M,M,M,M,M,M,M,M,M,M
US1MDCH0011,"PORT TOBACCO VILLAGE 1.0 S","CHARLES",56,38.50,-77.02,M,0.0,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,0.0,0.0,0.0,0.0,0.0,M,M,M
US1MDMG0003,"POTOMAC 0.9 NNW","MONTGOMERY",318,39.04,-77.21,M,1.2,M,M,M,M,M,M,M,M,0.4,M,M,M,M,M,M,M,M,0.9
US1MDMG0123,"POTOMAC 2.6 NE","MONTGOMERY",485,39.06,-77.17,M,1.3,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,1.8
US1MDCV0007,"PRINCE FREDERICK 1.4 W","CALVERT",148,38.55,-76.62,T,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.6
US1MDSS0001,"PRINCESS ANNE 2.1 SSW","SOMERSET",16,38.17,-75.71,M,0.4,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M
US1MDSS0004,"PRINCESS ANNE 4.4 WSW","SOMERSET",7,38.18,-75.77,0.0,0.5,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,0.0,M,M,0.0,T
US1MDQA0006,"QUEENSTOWN 2.6 S","QUEEN ANNE'S",15,38.95,-76.16,M,M,M,M,M,M,M,M,M,M,0.4,M,M,0.0,M,0.0,M,M,M,M
US1MDBL0035,"REISTERSTOWN 1.2 NW","BALTIMORE",729,39.47,-76.83,1.8,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,3.0,0.7,0.0,0.0,0.0,0.0,T,T,0.0,M
USC00187580,"REISTERSTOWN 2 NW","BALTIMORE",737,39.50,-76.84,M,0.0,0.0,0.0,0.0,0.0,0.0,M,0.0,0.0,M,M,0.0,0.0,0.0,0.0,0.0,M,M,M
US1MDSM0001,"RIDGE 1.0 N","ST. MARY'S",79,38.12,-76.37,M,M,0.0,0.0,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,0.7
US1MDMG0074,"ROCKVILLE 0.6 SE","MONTGOMERY",413,39.07,-77.15,0.0,M,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,M
US1MDMG0115,"ROCKVILLE 2.8 WNW","MONTGOMERY",449,39.09,-77.20,1.2,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.4,0.2,0.0,0.0,0.0,0.0,0.0,0.0,0.0,M,M
US1MDMG0055,"ROSSMOOR 1.3 ESE","MONTGOMERY",433,39.10,-77.05,0.0,1.4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.3,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.2
US1MDWC0016,"SALISBURY 2.1 SSE","WICOMICO",35,38.34,-75.57,M,0.0,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M
USW00093720,"SALISBURY-WICOMICO REGIONAL AIRPO","WICOMICO",47,38.34,-75.51,0.5,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,M,0.0,M
USC00188065,"SAVAGE RIVER DAM","GARRETT",1495,39.51,-79.14,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,1.3
US1MDAA0063,"SEVERN 1.0 SSE","ANNE ARUNDEL",0,39.12,-76.68,0.0,M,M,M,M,M,0.0,M,M,M,M,M,M,M,M,M,M,M,0.0,T
US1MDAA0051,"SEVERN 1.5 SSW","ANNE ARUNDEL",120,39.11,-76.70,M,2.2,M,M,M,M,M,M,M,M,T,M,M,M,M,M,M,M,M,T
US1MDAA0004,"SEVERN 2.0 W","ANNE ARUNDEL",224,39.13,-76.73,0.0,1.5,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,0.5,M,M,0.0,0.0,0.0,0.0,M,M,M
US1MDAA0066,"SEVERNA PARK 1.9 W","ANNE ARUNDEL",43,39.08,-76.61,0.0,M,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,M,M,0.0,M,0.0,0.0
USC00188207,"SHARPSBURG 5 S","WASHINGTON",500,39.40,-77.72,0.0,1.9,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.8,0.3,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.9
US1MDMG0114,"SILVER SPRING 1.4 ENE","MONTGOMERY",287,39.01,-77.03,M,1.1,M,M,M,M,M,M,M,M,M,M,M,M,0.0,M,M,M,M,0.5
US1MDMG0109,"SILVER SPRING 2.1 NNE","MONTGOMERY",339,39.04,-77.00,M,M,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,0.0,M
US1MDMG0106,"SILVER SPRING 6.4 NNE","MONTGOMERY",410,39.08,-77.01,M,1.2,M,M,M,M,M,M,M,M,0.0,M,M,M,M,M,M,M,M,M
USC00188315,"SINES DEEP CREEK","GARRETT",2040,39.52,-79.41,3.7,M,M,M,M,M,M,0.0,M,M,M,0.0,M,M,0.0,M,M,M,0.0,3.0
US1MDWR0014,"SNOW HILL 0.4 S","WORCESTER",21,38.17,-75.39,M,0.3,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M
US1MDTB0004,"ST. MICHAELS 0.7 SE","TALBOT",0,38.78,-76.21,T,1.6,0.0,0.0,0.0,0.0,M,M,0.0,0.0,T,M,M,0.0,M,0.0,M,M,0.0,0.0
USC00188557,"STEVENSVILLE 2SW","QUEEN ANNE'S",10,38.97,-76.34,0.0,1.8,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.1,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,M
US1MDPG0043,"SUITLAND 2.1 SE","PRINCE GEORGE'S",255,38.82,-76.91,T,1.0,0.0,0.0,0.0,0.0,M,0.0,M,0.0,T,M,M,0.0,M,0.0,0.0,M,0.0,T
US1MDHW0002,"SYKESVILLE 1.7 SSE","HOWARD",508,39.35,-76.97,0.0,1.4,0.0,0.0,0.0,0.0,M,M,0.0,0.0,0.9,0.3,M,0.0,0.0,0.0,0.0,M,0.0,M
US1MDMG0042,"TAKOMA PARK 0.6 NNW","MONTGOMERY",246,38.99,-77.01,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.5
US1MDCR0005,"TANEYTOWN 3.7 NE","CARROLL",535,39.69,-77.12,0.0,1.8,0.0,0.0,0.0,0.0,M,M,0.0,0.0,1.5,M,M,0.0,0.0,0.0,0.0,M,0.0,0.0
US1MDFR0010,"THURMONT 0.8 SSE","FREDERICK",501,39.61,-77.40,0.0,2.0,0.0,0.0,0.0,0.0,M,M,0.0,M,M,M,M,M,M,0.0,0.0,M,0.0,T
US1MDFR0030,"THURMONT 3.0 N","FREDERICK",1144,39.67,-77.40,0.1,3.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,2.6,2.3,T,0.0,0.0,0.0,0.0,T,0.0,T
US1MDBL0016,"TOWSON 0.8 SW","BALTIMORE",376,39.40,-76.62,0.0,1.8,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1.2,1.5,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
US1MDBL0043,"TOWSON 1.5 WNW","BALTIMORE",269,39.40,-76.65,M,1.4,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M,M
US1MDTB0009,"TRAPPE 3.5 NE","TALBOT",39,38.70,-76.02,T,3.0,0.0,0.0,0.0,0.0,M,M,0.0,0.0,M,M,M,0.0,0.0,M,0.0,M,0.0,M
US1MDFR0021,"URBANA 1.6 W","FREDERICK",437,39.33,-77.38,0.0,2.0,0.0,0.0,0.0,0.0,M,M,0.0,0.0,1.0,M,M,0.0,0.0,0.0,0.0,M,0.0,M
US1MDDR0009,"VIENNA 5.0 WNW","DORCHESTER",16,38.51,-75.91,0.0,1.5,0.0,0.0,0.0,0.0,M,0.0,0.0,0.0,M,M,M,0.0,0.0,0.0,0.0,M,M,M
US1MDCH0035,"WALDORF 3.3 S","CHARLES",200,38.59,-76.91,T,0.5,0.0,0.0,0.0,0.0,M,M,0.0,0.0,0.2,M,M,0.0,0.0,0.0,0.0,M,0.0,0.8
US1MDPG0085,"WEST LAUREL 1.1 N","PRINCE GEORGE'S",814,39.12,-76.90,M,M,M,M,0.0,0.0,M,M,M,M,M,M,M,0.0,M,0.0,0.0,M,0.0,1.9
US1MDBL0025,"WHITE MARSH 2.3 ESE","BALTIMORE",49,39.37,-76.42,0.0,2.0,0.0,0.0,0.0,0.0,M,M,M,0.0,1.5,0.5,M,0.0,0.0,0.0,0.0,M,0.0,0.0
US1MDMG0024,"WHITE OAK 1.2 N","MONTGOMERY",358,39.06,-76.99,T,1.2,0.0,0.0,M,M,M,M,M,M,0.4,0.0,M,M,M,M,M,M,M,0.5
US1MDWH0001,"WILLIAMSPORT 2.8 ENE","WASHINGTON",574,39.61,-77.77,M,2.4,0.0,M,M,M,M,M,M,M,1.5,1.0,M,M,M,M,M,M,M,0.0
US1MDKN0004,"WORTON 3.9 NW","KENT",49,39.32,-76.13,0.0,1.5,0.0,0.0,0.0,0.0,M,0.0,0.0,M,M,M,M,0.0,0.0,0.0,0.0,M,0.0,0.0

Tom · Posted 02-24-2019 03:33 PM

You can read a file like that with a simple data step.

You can add the DSD option to the INFILE statement (note that even if you paste the data into the program you will need to code an INFILE statement to add the DSD option).

You could read it in its current "wide" format.

missing m;
data want;
   infile datalines dsd truncover firstobs=2;
   length id $11 station $40 county $20 elevation  latitude longitude feb1-feb20 8;
   input id -- feb20;
datalines4;
"GHCN ID","Station Name",County,Elevation,Latitude,Longitude,"Feb 1","Feb 2","Feb 3","Feb 4","Feb 5","Feb 6","Feb 7","Feb 8","Feb 9","Feb 10","Feb 11","Feb 12","Feb 13","Feb 14","Feb 15","Feb 16","Feb 17","Feb 18","Feb 19","Feb 20"
USW00013701,"ABERDEEN PHILLIPS FLD","HARFORD",57,39.47,-76.17,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,M,M,2.5,4.2,0.0,0.0,0.0,M,M,M,M,M
US1MDFR0023,"ADAMSTOWN 0.7 ESE","FREDERICK",317,39.31,-77.46,0.0,1.9,M,M,0.0,M,M,M,M,M,M,M,M,M,0.0,M,0.0,M,M,M
US1MDAA0070,"ANNAPOLIS 3.2 NW","ANNE ARUNDEL",117,39.00,-76.55,M,0.0,0.0,0.0,0.0,0.0,M,M,0.0,M,M,M,M,0.0,0.0,0.0,0.0,M,0.0,M
;;;;

Or you could read it into a "tall" format.

missing m;
data want;
   infile 'myfile.txt' dsd truncover firstobs=2;
   length id $11 station $40 county $20 elevation  latitude longitude day snow 8;
   input id -- longitude @;
   do day=1 to 20;
     input snow @;
     output;
   end;
run;

creating data with Delimited Files

Re: creating data with Delimited Files

Re: creating data with Delimited Files

Re: creating data with Delimited Files

Re: creating data with Delimited Files

Re: creating data with Delimited Files

creating data with Delimited Files

Re: creating data with Delimited Files

Re: creating data with Delimited Files

Re: creating data with Delimited Files

Re: creating data with Delimited Files

Re: creating data with Delimited Files

Ready to join fellow brilliant minds for the SAS Hackathon?