About Q1983

Kurt_Bremser · ‎04-22-2021

Maxim 2: Read the Log. Your transpose fails because of duplicate ID values per group: 73 data have; 74 length section $25 75 month_ $5 76 tot_cnt 3 77 month_sort 8; 78 input section -- month_sort; 79 datalines; NOTE: SAS went to a new line when INPUT statement reached past the end of a line. NOTE: The data set WORK.HAVE has 8 observations and 4 variables. NOTE: Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit): real time 0.00 seconds cpu time 0.00 seconds 89 ; 90 run; 91 proc transpose data=have out=want let; 92 id month_; 93 var tot_cnt; 94 by section; 95 run; WARNING: The ID value "JAN20" occurs twice in derselben BY-Gruppe. WARNING: The ID value "FEB20" occurs twice in derselben BY-Gruppe. WARNING: The ID value "MAR20" occurs twice in derselben BY-Gruppe. WARNING: The ID value "APR20" occurs twice in derselben BY-Gruppe. NOTE: The above message was for the following BY group: section=central NOTE: There were 8 observations read from the data set WORK.HAVE. NOTE: The data set WORK.WANT has 1 observations and 6 variables. NOTE: Verwendet wurde: PROZEDUR TRANSPOSE - (Gesamtverarbeitungszeit): real time 0.01 seconds cpu time 0.00 seconds So you first need to decide how to deal with the duplicates (calculate a sum, a count, a mean?).

Patrick · ‎04-18-2021

One option is to use a SQL Union ALL. The variable names in the combined data set will have the names as per the first select statement. data have1; input x y; cards; 1 2 3 4 5 6 ; data have2; input r s; cards; 5 6 7 8 9 0 ; proc sql; create table combined as select x, y from have1 union all select r, s from have2 ; quit;

Patrick · ‎04-17-2021

Given that your past_due columns are mutually exclusive (=only one of them can be populated at a time) I believe it would be much easier if you create only a single column with a code in it. Having a code in a single column should make anything downstream much easier for you. /* option 1 */ data have2; set have; if Past_Due < 30 then past_due_status=0; else if Past_Due >= 30 and Past_Due <=40 then past_due_status=1; else if Past_Due > 40 and Past_Due <= 50 then past_due_status=2; run; /* option 2 */ proc format; invalue past_due_status low -< 30 = 0 30 -< 40 = 1 40 -< 50 = 2 50 - high = . ; run; data have2_x; set have; past_due_status=input(Past_Due,past_due_status.); run;

Patrick · ‎04-17-2021

@Q1983 Going forward please ask "unrelated" follow-up questions as a new question with a matching subject line. Not only will this make this forum more useful for people searching answers to questions already asked and resolved, it will also give your new question more attention as many people answering questions won't look into threads already marked as resolved. You can always specifically call out someone in your new question if you want them to become aware of your new question using "@<name>". This will create a notification for the mentioned user. Now to your question: If you've got only numerical variables to transpose then the resulting transposed column will be numerical. SAS formats apply on columns (variables) and not on cells. Because the variables you transpose have different formats SAS has to use a default format on the transposed column (I believe that's best32.). In the docu for the Var statement of Proc Transpose here you can find: To have different formatting per cell you need to convert the numerical values to strings. And for Proc Transpose to do so one of the variables must be of type character. For conversion numeric to character Proc Transpose will use the format assigned to the variable (or the default of best32. if there is no explicit format defined). Below two options how you can get what you're after. Please note that if using the first option there can be leading blanks in the transposed values. %let mth4=%sysfunc(intnx(month, %sysfunc(today()),-4), monyy5.); %let mth3=%sysfunc(intnx(month, %sysfunc(today()),-3), monyy5.); %put &mth4 &mth3; data have; Category='MyCategory'; count = 7000; trans_4 = 0.9983; trans_3 = 0.8988; format trans_4 trans_3 percent8.2; run; /** option 1 **/ data want1; set have; length _dummy $1; call missing(_dummy); rename trans_3=&mth3 trans_4=&mth4; run; /* add _dummy to var statement to "force" proc transpose to create a character variable */ /* for the transposed values. Drop _dummy from output as not wanted there */ proc transpose data=want1 out=want2(where=(upcase(_name_) ne '_DUMMY')) let; /* if desired explicit format statements allows to overwrite formats from source */ format count comma.; var _dummy count &mth4 &mth3; by Category; run; /** option 2 **/ /* -l option in put statement to left align result */ data want1; set have; count_c = put(7000,best32. -l); &mth3=put(trans_3,percent8.2 -l); &mth4=put(trans_4,percent8.2 -l); run; proc transpose data=want1 out=want2 let; var count_c &mth4 &mth3; by Category; run;

mkeintz · ‎04-17-2021

@ballardw wrote: Order of variables in a data set is one of the lowest priority things I can think of doing though apparently many people seem to think it is needed. Anything done inside SAS basically does not care. If a file you are sending to an output file needs a specific order then create that file at that time. Otherwise you can spend a lot of time fooling around with (ever changing) minutia that actually wastes time and makes code hard to follow. So, why is a specific order of variables/ variable names needed? I can think of a few activities I commonly do in which knowing/using variable order is "needed": When displaying the data Expecting the identifier variables (id/date/time/...) to consistently be in a particular position Variable clusters - i.e. 6 groups of 5 attributes arranged contiguously True, the above are for looking at the data, not programming. But I often take advantage of variable order in programming as well, mostly through use of the double dash as a means of generating a list of variable names or array declarations, typically to avoid listing lots of variable names. Or it can also be useful when I might not actually know the variable names of interest. For example: let's say that I want to interleave A and B, but keep only the variables present in dataset A: data want ; if 0 then set a; retain _sentinel1 .; if 0 then set b; retain _sentinel2 .; set a b; by id; drop _sentinel1--_sentinel2; run; Yes, this can be done directly in SQL, but there are often many reasons to use a data step instead.

andreas_lds · ‎04-08-2021

Your dataset "want" looks like a report. Do you really need a dataset? If not, try proc tabulate: proc tabulate data=have format=5.; class Mgr Exception; var cnt; table Mgr= ' ', Exception*cnt= ' ' / box= 'Mgr'; keylabel sum= ' '; run;

Kurt_Bremser · ‎04-06-2021

That's highly illogical; since the category is derived from BOTH dates, there cannot be a change from one date to the other, so the report as posted makes no sense and is factually wrong.

ChrisNZ · ‎04-03-2021

1. Paste your code using the appropriate icon 2. The expected result looks like a report. Maybe a proc tabulate result. Is this what you want? 3. I cannot understand what the percentages represent.

Patrick · ‎03-28-2021

If you can be sure that the column attributes (especially type and length) are the same in all source ds then you could also do something like below (code not tested) in case you want to create data which you can report on in a single report. proc sql noprint; select cats(libname,'.',memname,'(keep=',name,')') into :ds_list separated by ' ' ,memname as table_name from dictionary.columns where libname = 'LAKEP' /*and user_id in ('1LB','402','CM7','EI9','JS2','J8M', 'KS7','MB2','NR4','VG5') or username in ('1LB','402','CM7','EI9','JS2','J8M', 'KS7','MB2','NR4','VG5')*/ ; quit; data all; length _source_ds sourc_ds $41; set &ds_list inds=_source_ds; source_ds=_source_ds; run;

Kurt_Bremser · ‎03-28-2021

This needs to be fixed at the point where the data enters your SAS environment. Date values must (I use that word intentionally here) always be stored as SAS dates. From where does the data originate (database, text file, Excel spreadsheet, ...)? Next, you need to use ANSI SQL and SAS syntax, the available columns, and the functions available in SAS. I guess you want this: where intnx('month',today(),0,'b') le my_date le intnx('month',today(),0,'e') or where month(my_date) = month(today()) and year(my_date) = year(today()) or where put(my_date,yymmn6.) = put(today(),yymmn6.)

Tom · ‎03-21-2021

Why not try it and see? proc sql; create table WANT as select make , model , type , count(*) as count from sashelp.cars where make in ('Audi') ; quit; But that will put the count on every observation. If you want to count the number by MAKE use GROUP BY create table want as select make,count(*) as count from sashelp.cars group by make ; Or just use PROC FREQ. proc freq data=sashelp.cars; tables make; run;

ballardw · ‎03-15-2021

Not sure exactly why you are testing 4 characters. If this is US and related phone numbers you have two section to check for 000- the first 3 are area code and next three are exchanges. I know from processing phone numbers I have seen invalid exchanges, like 000 when the area code was valid: 208-000-0000 for example. Array processing would take two arrays such as: data have; length borr_ph $10 co_borr_ph $10 alt_ph $10 ; input borr_ph -- alt_ph; array ph (*) borr_ph -- alt_ph; array np (3) no_borr_ph no_co_borr_ph no_alt_ph ; do i= 1 to dim(ph); np[i] = (substr(ph[i],1,4) in('0000','1111','2222','3333','4444','9999') ); end; drop i; datalines; 8042234314 0000000000 3334444444 0000000000 1111111111 9999999999 2132245566 1121111111 2321111111 1111114444 0000000000 1111111111 1112223333 8042342256 0000000000 ; I prefer numeric 1 (true)/0 (false) coded variables instead of 'Y' as there are more reporting options PLUS SAS will return a numeric 1/0 for comparisons. So cuts down on specific "Then var = 'Y';" type codes. For instance, if you need to know if ALL of the values are "true" instead of 3 (or 20) if var='y' and var2='y' .. you can use: No_phone = min(of np(*) ); Since the NP array elements will be 0 or 1 if at least one of the values has a valid phone (assuming those checks worked correctly) then the Min value for the array will be 0. If all are not valid then the min is 1 , or the "not a phone". Notice that the " of arrayname(*) " syntax does not care at all about how many variables are in the array. So the length of the syntax doesn't change. Character variables take a bit more work, not much but some. Would suggest looking a bit more into proper numbers. US for example US phones all use one single format: (NXX) NXX-XXXX, where N is 2-9 and X is 0-9. So area code cannot start with 0 or 1, neither can exchange (the second 3 characters). If you really must see a Y/N then a custom format for 1/0 coded values can do that.

Q1983 · ‎03-10-2021

Thanks, I can use this in similar code. Appreciate this

Tom · ‎01-13-2021

Note that it is probably a lot faster to just type in the 12 numbers than it is to create and attach a spreadsheet. JAN20 FEB20 TOT_CNT 33 TOT_CNT 25 CURRENT STAYED 98 STAYED 2 ROLLED 2 ROLLED 4 TOT_CNT 10 TOT_CNT 7 DEL_30 STAYED 3 STAYED 2 ROLLED 1 ROLLED 1 Why did you post the SQL code? It is not doing anything. If you want to display both the values of MONTH_SUM and TOT_CNT in the same field of a report then put them into the same variable in the SAS dataset. data have; length catgy $25 month_ $5 tot_cnt 8 descr $25 descr_pct monthsum 8 ; input catgy -- monthsum; datalines; CURRENT JAN20 33 STAYED 98 1 CURRENT JAN20 33 ROLLED 2 1 CURRENT FEB20 25 STAYED 2 2 CURRENT FEB20 25 ROLLED 4 2 DEL_30 JAN20 10 STAYED 3 3 DEL_30 JAN20 10 ROLLED 1 3 DEL_30 FEB20 7 STAYED 2 4 DEL_30 FEB20 7 ROLLED 1 4 ; data have2 ; set have; by catgy month_ notsorted; stat = monthsum; output ; if last.month_ then do; descr = 'TOT_CNT'; stat = tot_cnt; output; end; run; Now you can make a report from that new dataset. proc report data=have2 ; column catgy descr stat,month_ ; define catgy / group ; define descr / group ; define month_ / across ' ' order=data; define stat / sum ' '; run; catgy descr JAN20 FEB20 CURRENT ROLLED 1 2 STAYED 1 2 TOT_CNT 33 25 DEL_30 ROLLED 3 4 STAYED 3 4 TOT_CNT 10 7

Tom · ‎01-07-2021

Looks to me like you have imported an Excel spreadsheet where one of the column had some datetime values and some character strings. So SAS had to create the variable as character. When it does that the datetime values are stored as strings that represent the number Excel uses to store the value. First convert the value to a number using INPUT() then adjust to value by the difference in basis dates used for counting days. date2=input(date1,32.)+'30DEC1899'd ; If you need the time component you will need to convert the value from days to seconds. In Excel the 0.70833333334 means what percent of 24 hours. date3=dhms(date2,0,0,0); Then attach the display format you want to use for these two new variables. format date2 date9. date3 datetime19. ; Results: 214 data have; 215 date1='44204.70833333334'; 216 date2=input(date1,32.)+'30DEC1899'd ; 217 date3=dhms(date2,0,0,0); 218 format date2 date9. date3 datetime19. ; 219 put (_all_) (=/); 220 put (date2 date3) (= comma20. /); 221 run; date1=44204.70833333334 date2=08JAN2021 date3=08JAN2021:17:00:00 date2=22,289 date3=1,925,744,400

Online Status	Offline
Date Last Visited	‎05-08-2023 02:16 PM

Date will not change to Character

Date reading as 1960 after conversion

YYYMMDD where the field reads as a date

Change decimal number to a character

Suppress fields based on dataset in ods output

Proc report conditional fields

Re: Import CSV issue

Import CSV issue

Datetime format question

Date not displaying from database properly

Re: Date will not change to Character

Re: Date reading as 1960 after conversion

Re: YYYMMDD where the field reads as a date

Re: Suppress fields based on dataset in ods output

Re: Headers on first two rows of csv for export

output based on variable in dataset

Defined variable for file extension

Re: Headers on first two rows of csv for export

Re: Rename dynamic month to char and order desc

Re: Stack datasets vertically despite different variable names

Re: proc transpose collapse multiple rows into single row

Re: Rename numeric decimal to month label in datastep

Re: Rename and re-position variables on same row - Proc Transpose

Re: Rename var in proc transpose??

Re: Pivot Data Proc Report vs Proc Transpose??

Re: Display totals as percentage before or after proc transpose

Re: query a libname for variable and contents

Re: First and Last day using proc - date is character

Re: Showing total throughout the proc sql or datastep

Re: Substring and possible array testing

Re: Add total conditionally in datastep, proc summary or proc report

Re: Pivot Dataset

Re: Convert char to Date