About Steelers_In_DC

Steelers_In_DC · ‎12-16-2015

I got a little mixed up based on your description of input - output. Let me know if this is the solution you are looking for, cheers: data have; infile cards dsd; input ID01$ FirstName$ LastName$ Element1Element2Element3 $50.; cards; ID01,Mark,Johnson,test1|test2|test3 ; data prep(drop=Element1Element2Element3); set have; var1=scan(Element1Element2Element3,1,'|'); var2=scan(Element1Element2Element3,2,'|'); var3=scan(Element1Element2Element3,3,'|'); run; proc transpose data=prep out=want(drop=_NAME_)prefix=Element;by ID01 firstname lastname;var var:;

Steelers_In_DC · ‎12-16-2015

Are you working with this within SAS or are you passing through to somewhere else, Oracle, Teradata etc?

Steelers_In_DC · ‎12-14-2015

Here is a solution: data have; input Animal age weight; cards; 1 11 10 1 21 10.2 1 31 10.21 1 41 10.22 1 45 10.45 1 50 10.6 2 43 11 2 45 11.3 2 49 11.35 2 60 11.3 2 65 12.2 2 70 12.5 2 76 12.65 2 80 13 2 95 14 2 105 16 3 25 10 ; proc sql; create table want as select animal,avg(weight) as Weight_Average from have group by animal;

Steelers_In_DC · ‎12-14-2015

This will pull only the variables that are in B and not A: data a; input var1 var2; cards; 1 2 ; run; data b; input var1 var2 var3; cards; 1 2 3 ; run; proc transpose data=a out=tran_a (keep=_NAME_); proc transpose data=b out=tran_b (keep=_NAME_); data a_vars b_vars both_vars; merge tran_a(in=a) tran_b(in=b); by _NAME_; if a and not b then output a_vars; if b and not a then output b_vars; if a and b then output both_vars; run; proc sql noprint; select _NAME_ into :var_names separated by ',' from b_vars; proc sql; create table want as select &var_names from b;

Steelers_In_DC · ‎12-14-2015

Here is a solution: data have; input values$; cards; ABC123 12AB45 12345A A12D52 123456 ; data want; set have; NUMERIC = prxchange("s/[^0-9]//",-1,values); CHARACTERS = prxchange("s/[^A-Z]//",-1,values); NUMBERS = input(prxchange("s/[^0-9]//",-1,values),8.); run;

Steelers_In_DC · ‎12-11-2015

I'm still not clear what you want. I think you are saying you want 16 new variables. If that is the case this is a solution. Still has the two sorts in it which I'd like to work around: data have; infile cards dsd; input ca mois jour an ident_num ident_temp semain_num; cards; 2427.93,1,3,2002,1,1,4 2284.22,1,4,2002,1,2,5 2544.94,1,5,2002,1,3,6 2055.43,1,7,2002,1,4,1 2247.45,1,8,2002,1,5,2 2891.51,1,9,2002,1,6,3 2571.43,1,10,2002,1,7,4 2646.82,1,11,2002,1,8,5 3096.43,1,12,2002,1,9,6 2173.95,1,14,2002,1,10,1 2243.97,1,15,2002,1,11,2 2304.2,1,16,2002,1,12,3 2388.01,1,17,2002,1,13,4 2435.16,1,18,2002,1,14,5 2593.02,1,19,2002,1,15,6 2012.54,1,21,2002,1,16,1 2223.43,1,22,2002,1,17,2 2086.49,1,23,2002,1,18,3 2342.84,1,24,2002,1,19,4 2180.02,1,25,2002,1,20,5 2120.58,1,26,2002,1,21,6 1943.99,1,28,2002,1,22,1 2169.63,1,29,2002,1,23,2 2197.35,1,30,2002,1,24,3 2006.92,1,31,2002,1,25,4 2310.02,2,1,2002,1,26,5 2126.89,2,2,2002,1,27,6 2077.39,2,4,2002,1,28,1 1928.1,2,5,2002,1,29,2 2098.27,2,6,2002,1,30,3 2199.86,2,7,2002,1,31,4 2355.6,2,8,2002,1,32,5 1950.73,2,9,2002,1,33,6 2118.97,2,11,2002,1,34,1 2348.87,2,12,2002,1,35,2 2443.23,2,13,2002,1,36,3 2602.99,2,14,2002,1,37,4 2162.27,2,15,2002,1,38,5 1927.4,2,16,2002,1,39,6 1964.16,2,18,2002,1,40,1 2175.51,2,19,2002,1,41,2 2194.22,2,20,2002,1,42,3 2296.39,2,21,2002,1,43,4 2200.81,2,22,2002,1,44,5 2005.12,2,23,2002,1,45,6 1967.49,2,25,2002,1,46,1 2125.22,2,26,2002,1,47,2 2087.93,2,27,2002,1,48,3 2093.22,2,28,2002,1,49,4 2245.66,3,1,2002,1,50,5 2444.07,3,2,2002,1,51,6 2158.51,3,4,2002,1,52,1 2074.35,3,5,2002,1,53,2 2052.68,3,6,2002,1,54,3 2393.61,3,7,2002,1,55,4 2407.32,3,8,2002,1,56,5 2267.7,3,9,2002,1,57,6 2166.9,3,11,2002,1,58,1 2266.97,3,12,2002,1,59,2 2317.02,3,13,2002,1,60,3 2191.15,3,14,2002,1,61,4 2387.77,3,15,2002,1,62,5 2432.63,3,16,2002,1,63,6 2096.73,3,18,2002,1,64,1 2088.37,3,19,2002,1,65,2 2185.96,3,20,2002,1,66,3 2419.34,3,21,2002,1,67,4 2548.06,3,22,2002,1,68,5 2056.63,3,23,2002,1,69,6 2105.71,3,25,2002,1,70,1 2082.3,3,26,2002,1,71,2 2353.34,3,27,2002,1,72,3 2475.71,3,28,2002,1,73,4 2224.62,3,30,2002,1,74,6 2348.35,4,2,2002,1,75,2 2349.47,4,3,2002,1,76,3 2376.97,4,4,2002,1,77,4 2169.63,4,5,2002,1,78,5 2344.63,4,6,2002,1,79,6 2047.11,4,8,2002,1,80,1 2273.1,4,9,2002,1,81,2 2134.35,4,10,2002,1,82,3 2043.25,4,11,2002,1,83,4 2189.95,4,12,2002,1,84,5 1969.94,4,13,2002,1,85,6 2106.33,4,15,2002,1,86,1 2292.86,4,16,2002,1,87,2 2440.9,4,17,2002,1,88,3 2374.14,4,18,2002,1,89,4 2364.45,4,19,2002,1,90,5 2052.59,4,20,2002,1,91,6 2021.61,4,22,2002,1,92,1 2193.04,4,23,2002,1,93,2 2286.61,4,24,2002,1,94,3 2299.07,4,25,2002,1,95,4 2139.42,4,26,2002,1,96,5 2120.9,4,27,2002,1,97,6 1894.11,4,29,2002,1,98,1 2089.68,4,30,2002,1,99,2 2424.98,5,2,2002,1,100,4 2418.49,5,3,2002,1,101,5 2181.87,5,4,2002,1,102,6 2139.99,5,6,2002,1,103,1 2329.11,5,7,2002,1,104,2 2275.18,5,10,2002,1,105,5 1921.58,5,11,2002,1,106,6 1979.94,5,13,2002,1,107,1 2076.57,5,14,2002,1,108,2 2288.07,5,15,2002,1,109,3 2464.58,5,16,2002,1,110,4 2064.54,5,17,2002,1,111,5 2117.22,5,18,2002,1,112,6 2179.8,5,21,2002,1,113,2 2029.69,5,22,2002,1,114,3 2259.05,5,23,2002,1,115,4 2390.29,5,24,2002,1,116,5 2092.5,5,25,2002,1,117,6 2021.46,5,27,2002,1,118,1 2218.12,5,28,2002,1,119,2 ; proc sort data=have;by descending ident_temp; data want; set have; %macro lag; %do i = 1 %to 16; lag&i=lag&i(ca); %end; %mend lag; %lag; run; proc sort data=want;by ident_temp;

Steelers_In_DC · ‎12-11-2015

There is most likely a more elegent way to do this without the sorts but here is a solution: data have; infile cards dsd dlm= ' '; input ca mois jour an ident_num ident_temp semain_num; cards; 2427.93 1 3 2002 1 1 4 2284.22 1 4 2002 1 2 5 2544.94 1 5 2002 1 3 6 2055.43 1 7 2002 1 4 1 2247.45 1 8 2002 1 5 2 2891.51 1 9 2002 1 6 3 ; proc sort data=have;by descending ident_temp; data want; set have; lag1=lag1(ca); lag2=lag2(ca); lag3=lag3(ca); lag4=lag4(ca); lag5=lag5(ca); run; proc sort data=want;by ident_temp;

Steelers_In_DC · ‎12-11-2015

With no example data or desired output this is just a random guess, here goes: data a; input id; cards; 1 2 3 9 10 ; data b; input id; cards; 1 2 3 4 5 6 7 8 9 10 ; data want; merge a(in=a) b(in=b); by id; if b; if b and not a then count+1; run;

Steelers_In_DC · ‎12-11-2015

Give an example of the data you have and what you want. I'm having trouble imagining: looking for 'missing variable' and adding record with value of 0.

Steelers_In_DC · ‎12-09-2015

Here is a solution: data have; input Subject$ Score; cards; a 1 a . a 3 a 7 b 4 b . b 1 b 7 ; proc sql; create table prep as select *,avg(coalesce(score,0)) as mean format = number.2 from have group by subject; data want; length subject $50.; set prep; by subject; if last.subject then do;output; subject = catx(' ','Average of',subject,'= '); score= mean; end; drop mean; output; run; Be sure to note the cautions/advice for trying to keep up with current Mean values for these variables. PROC REPORT is suited to create mean (and other aggregations) summaries for reporting purposes.

Steelers_In_DC · ‎12-09-2015

I am going through some training myself so there are most likely better sources but I will explain something as I understand it to join the conversation: data want; merge emphours (in=a) employees(in=b); by empid; if a; run; data empinfo; length empid $6. empln $30. empfn $25. empmi $1. gender $1. startdate 8. emppaylevel $5.; if _N_ = 1 then do; declare hash new(dataset: 'employees',ordered: 'y'); new.definekey('empid'); new.definedata('empln','empfn','empmi','gender','startdate','emppaylevel'); new.definedone(); call missing(empln,empfn,empmi,gender,startdate,emppaylevel); end; set emphours; rc = new.find(); drop rc; format startdate mmddyy10.; run; The two pieces of code produce the same results, definekey is the key in the hash table that I am generating and the field that I will be joining on. definedata() is the fields that I want to pull in. This is a simple merge, I am pulling fields from the employee dataset, joining to the emphours dataset. This is a very simple example for hash. Hope I provided some help. Mark

Steelers_In_DC · ‎12-08-2015

You have to remain consistent with date or datepart. data have; range1 = '15SEP2015:18:00:00.000000'dt; range2 = '25SEP2015:18:00:00.000000'dt; p_create = '20SEP2015:18:00:00.000000'dt; format range1 range2 p_create datetime25.; run; data want; set have; if range1 < p_create < range2 then yes = 'yes'; if datepart(range1) < '20sep2015'd < datepart(range2) then check = 'yes'; run;

Steelers_In_DC · ‎12-08-2015

I was hoping someone else answered so I can see the solution. I'm not the best person to answer this question but see if this gets the desired results, with the test data it works: data want; length acctnum 8. promisedate 8. promiseamt 8. bwindow 8. ewindow 8.; if _N_ = 1 then do; declare hash e(dataset:'promise'); e.definekey('acctnum'); e.definedata('promisedate','promiseamt','bwindow','ewindow'); e.definedone(); end; set Payments; drop rc; rc = e.find(); if rc = 0 and bwindow <= paydate <= ewindow; format promisedate bwindow ewindow mmddyy10.; run;

Steelers_In_DC · ‎12-08-2015

Without an example dataset to work from I am guessing and making this up as I go along. This will give you each individual ID number and how many columns they have that are matching. It does not show what the differences are, only the amount of columns that are the same between two datasets: data one; input id$ var1 var2 var3; cards; 100 1 1 1 200 2 2 2 300 3 3 3 ; data two; input id$ var1 var2 var3; cards; 100 1 0 0 200 1 1 1 300 3 3 3 ; proc transpose data=one out=tranone;by id; proc transpose data=two out=trantwo(rename=(col1 = COL2));by id; data prep; merge tranone(in=a) trantwo(in=b); by id; if a and b and col1 = col2; count + 1; if first.id then count = 1; run; data want; set prep; by id; if last.id then output; keep id count; run;

Steelers_In_DC · ‎12-08-2015

I am missing something, can you explain why the second record does not fall into the criteria?

Online Status	Offline
Date Last Visited	‎07-12-2025 12:12 PM

sql server using 'with'

Re: hive show table and column

hive show table and column

proc sql using or

find edge node

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Fill in rows with previous value if next value is the same.

Re: reg ex extract

Re: proc sql using or

Re: Adding quotes to a Macro Variable

Re: How to run a program in EG5.1 without connecting to a server

Re: macro variable in set statements based on today()

Finding all single linked chains: the allChains macro

find edge node

infile 'red x' with no error in log

Re: Extracting Month, Year and quarter

Re: Which Date function can be used to extract month(Character) from t...

Re: How to assign a column value based on another column ??

Re: How do I duplicate rows with different element from a field

Re: substract text from another text

Re: Obtain the general mean in data set by categories

Re: 2 dataset, list different observations

Re: Extracting character and numeric values in different variables

Re: Reshape long data into wide data with lag

Re: Reshape long data into wide data with lag

Re: i have 2 datasets and i want to know number of missing obs from da...

Re: How to create dummy data for reporting

Re: Calculate average of variable and add as observation

Re: Define key and define data in hashing

Re: Date / Time Formatting Issue

Re: Hash Question

Re: Checking for duplicates

Re: Hash Question